SQL Server’s 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 stored procedure, 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;

Soup Sandwich


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

A SQL Server query plan
Why are there two with the same plan?

Contestant Number Two

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!

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.



9 thoughts on “SQL Server’s 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. John — IIRC, using a local variable in top gives you a flat guess of 100. Is that what you saw?

      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.

Comments are closed.