October 8, 2018 at 6:16 pm
Hello,
I have this stored proc I am trying to determine if it can be optimized for better performance. If anyone could advise it would be appreciated.
Please find attached script containing table, stored proc and inner functions.
October 9, 2018 at 3:39 am
Oh there is so much that can be optimised in there.
Table variables - slow, avoid, use temp tables instead.
Multiple execution paths - erratic performance, avoid
Multi-statement table-valued functions - slow, avoid.
Catch-all queries - erratic performance, avoid.
There's enough tuning work in there to keep someone busy for days. Here's some reading material:
https://www.red-gate.com/simple-talk/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/
https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2018 at 4:12 am
Honestly, it looks like this thing is ticking every single code smell in this list. Go through this article and apply everything.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply