September 8, 2021 at 4:40 pm
Hello,
we need a great Article about how to understand complex queries (with a lot of subquries) fast.
Are their methods available?
September 8, 2021 at 8:13 pm
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.
September 9, 2021 at 2:51 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 9, 2021 at 8:02 pm
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!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply