What Goes Wrong
I still see people calling into this trap when writing stored procedures. What no one seems to realize until it’s too late data grows past a toy data base size, is that SQL Server’s query optimizer doesn’t respect IF branches as boundaries in the way you’d expect.
Sure, the control-flow logic boundaries are respected, but the big surprise to most people comes from how the optimizer treats query plans in IF branches: It compiles them all no matter what.
In the video below, I’ll show you exactly what that means, and how it can really screw up performance. This video is a small part of my paid training offering, and if you enjoy it, there’s a link at the end of the post to get 75% off.
IF Branching Video!
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
Related Posts
- Software Vendor Mistakes With SQL Server: Dealing With Bad Parameter Sniffing
- Software Vendor Mistakes With SQL Server: Writing And Optimizing Paging Queries
- Software Vendor Mistakes With SQL Server: Handling Optional Parameters
- Software Vendor Mistakes With SQL Server: Using Left Joins To Find Rows That Don’t Exist
Very nice work!. I have 2 questions:
1) I heard another trick: create dummy variables on top of the stored procedure which gets the value of the parameters. Instead of “WHERE xx = @parameter” it will be “WHERE xx = @var” is this a good replacement for dynamic SQL?
2) On which SQL version are you running these tests? I thought these “issues” were fixed a little in SQL2019
1. You’re hurting my feelings, here.
2. No, not at all. Not even close.
sorry to hurt your feelings. About question 1, I forgot to add “a friend of my said …”