SQL Server IF Branches And Query Performance Part 1: The Problem

Manifesto

---

This is a problem I deal with quite a bit when helping people track down performance problems and, you know, solve them.

The basic scenario is something like this:

CREATE PROCEDURE
    dbo.iffy_kid
(
    @p1 int,
    @p2 int,
    @decider varchar(10)
)
AS
SET NOCOUNT, XACT_ABORT ON;

IF @decider = 'this_table'
BEGIN

    SELECT
        this.*
    FROM dbo.this_table AS this
    WHERE this.this_column = @p1;

END;

IF @decider = 'that_table'
BEGIN

    SELECT
        that.*
    FROM dbo.that_table AS that
    WHERE that.that_column = @p2;

END;

ELSE
BEGIN

    /*Do something else*/

END;

You have some parameter that decides which logical execution path that a query will take, and different queries that run based on that path.

What this does not control is query optimization paths, or cardinality estimation paths, at least not written in this manner.

First Blood

---

When this stored procedure is executed for the first time, or when some recompilation event happens, both queries will get a query plan generated and cached.

For simplicity, let’s say that when a query plan is cached, it it’s compiled and executed with

• @p1 = 100
• @p2 = NULL
• @decider = ‘this_table’

SQL Server’s query optimizer will generate a query plan for the entire stored procedure based on cardinality estimation for:

• @p1 = 100 as a predicate on this_table
• @p2 = NULL as a predicate on that_table

On future executions, if the runtime execution parameters change to:

• @p1 = NULL
• @p2 = 200
• @decider = ‘that_table’

The query plan with cardinality estimation for @p2 = NULL will be reused.

You’ve essentially multiplied any parameter sensitivity issue by:

• The number of separate IF branched queries
• The number of parameters fed into the stored procedure

Exploration

---

Over the rest of the week, I’m going to cover this topic from a few different angles to show you what works and what doesn’t work for fixing the problem.

Clients that I work with are often very surprised by the gotchas, intricacies, and weird details that crop up when writing queries like this.

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.