Falling For Rome
Okay, so like, an IF branch in a stored proc can be helpful to control logic, but not to control performance.
That’s the most important line in the blog post, now lemme show you why.
All Possible Permutations Thereof
Let’s say for our proc, we want to use a different plan for different TOPs, and our tipping point is 10,000.
That’s the tip of our top, if you will. And you will, because my name’s on the blog, pal.
CREATE OR ALTER PROCEDURE dbo.top_sniffer (@top INT, @vtid INT) AS BEGIN IF @top <= 10000 BEGIN SELECT TOP (@top) v.Id, v.PostId, v.UserId, v.BountyAmount, v.VoteTypeId, v.CreationDate FROM dbo.Votes AS v WHERE v.VoteTypeId = @vtid ORDER BY v.CreationDate DESC; END IF @top > 10000 BEGIN SELECT TOP (@top) v.Id, v.PostId, v.UserId, v.BountyAmount, v.VoteTypeId, v.CreationDate FROM dbo.Votes AS v WHERE v.VoteTypeId = @vtid ORDER BY v.CreationDate DESC; END END;
This goes quite poorly. If we just get estimated plans, here’s that they produce.
/*Run me*/ EXEC dbo.top_sniffer @top = 1, @vtid = 2; /*Run me*/ EXEC dbo.top_sniffer @top = 5000000, @vtid = 2;
Contestant Number One
Contestant Number Two
Look Both Ways
The optimizer explores both paths, and the plan cache concurs.
If you were to run it with the higher value first, you’d see the same thing for the parallel plans.
Logic, Not Performance
Making plan choices with IF branches like this plain doesn’t work.
The optimizer compiles a plan for both branches based on the initial compile value.
What you end up with is a stored proc that doesn’t do what it’s supposed to do, and parameter sniffing times two.
For a lot more information and examples, check out this Stack Exchange Q&A.
Thanks for reading!