The Not Very Mighty IF Branch

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.

Soup Sandwich

This goes quite poorly. If we just get estimated plans, here’s that they produce.

Contestant Number One

Why are there two with the same plan?

Contestant Number Two

Why is this the same thing?

Look Both Ways

The optimizer explores both paths, and the plan cache concurs.

Dead giveaway

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!

9 thoughts on “The Not Very Mighty IF Branch”

      1. I don’t have access to a SQL server from my iPhone at home, but what about using OPTION (OPTIMIZE FOR (@top = 100)) in one query and a far greater value in the second? Not sure if I got that syntax correct.

  1. Adding a local variable, while not particularly advisable, e.g. declare @big int = @top, and using @big for the 2nd block seems to shake things up.

      1. Indeed, that was a lead balloon. Apart from the previous suggestions, then, I’d throw in the towel on IF, affirming the point of this article, and just set the @top filter as predicate in respective UNION blocks. I got correct row estimates and the plus-sized @top got a parallel plan that way.

Leave a Reply

Your email address will not be published. Required fields are marked *