Article about Query Understanding

  • Hello,

    we need a great Article about how to understand complex queries (with a lot of subquries) fast.

    Are their methods available?

  • I'm no expert, but you have to unravel SQL  from the innermost queries to the outermost.

    is there a shortcut? Not that I know of.  I'd love to be proven wrong, though.

  • Total agreement with @pietlinden. There is no "fast" way to understand insanely huge and complex queries. It's all about breaking down the component parts and understanding what each does. That just takes time. The good news is, most people have patterns they follow when coding. Eventually, you'll spot those patterns and things will speed up a bit. Otherwise, tuning a 50,000 line script is the same as tuning a 5 line script, just multiplied 10,000 times.

    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • I agree with Grant and Piet here - there are no shortcuts with it.

    One thing that can help though and I strongly recommend is to adopt a coding standard AND enforcing them.  To add to what Grant said, having a 5 line script MAY be 50,000 lines long when properly formatted.  Our ERP system has all of its stored procedures written on a single line.  scrolling over to read that is painful, but run a code formatting tool (like SQL Prompt or Poor Man's TSQL Formatter) and that single line expands out into something that is easier to read and debug.  Sometimes that single line breaks out to 15 lines, sometimes it is 1,500 or more.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply