IF/ELSE Performance in Stored Procedures

Question

I found this article that explains using IF/ELSE statements in a SP can cause performance deterioration over using separate SPs for each 'branch'. http://sqlmag.com/t-sql/if-statements-and-stored-procedure-performance

But I have an SP which selects the same columns, from the same tables, and only the WHERE clause changes depending on what variables are present. Here is an example:

IF @Variable1 IS NOT NULL 
 BEGIN
   SELECT 
        *
   FROM
     dbo.Table1
   WHERE
     Column1 = @Variable1
  END
ELSE IF @Variable1 IS NULL AND @Variable2 IS NOT NULL
 BEGIN
  SELECT 
   *
  FROM
   dbo.Table1
  WHERE
   Column1 = Column1 
   AND
   Column2 = @Variable2
 END

So in this example, is it better to have 2 seperate SPs to handle the different variables or is it ok to have it all in one like this? (I know using SELECT * is not good practice. I just did it for the sake of example)

Answer 1

Normally, I wouldn't worry about this, although you should look at the white paper referenced by Mikael Eriksson which has a copious amount of useful information on this subject. However, I would remove the Column1 = Column1 statement in the else branch, because that could potentially confuse the optimizer.

What the article is referring to is the fact that the stored procedure is compiled the first time it is run. This can have perverse results. For instance, if the table is empty when you first call it, then the optimizer might prefer a full table scan to an index lookup, and that would be bad as the table gets larger.

The issue may be that one of the branches gets a suboptimal performance plan, because the data is not typical on the first call. This is especially true if one of the values is NULL. This doesn't only occur with if, but that is one case where you need to be sensitive to the issue.

I would recommend the following:

• If your tables are growing/shrinking over time, periodically recompile your stored procedures.
• If your tables are representative of the data, don't worry about splitting into multiple stored procedures.
• Your examples should do an index lookup, which is pretty simple. But monitor performance and check execution plans to be sure they are what you want.
• You can use hints if you want to force index usage. (Personally, I have needed hints to force particular join algorithms, but not index usage, but I'm sure someone else has had different experiences.)

For your examples, an index on table1(column1) and table1(column2) should suffice.

The summary of the advice is not to fix this until you see there is a problem. Putting the logic into two stored procedures should be for fixing a problem that you actually see, rather than pre-empting problem that may never exist. If you do go with a two-procedure approach, you can still have a single interface that calls each of them, so you still have the same API. In other words, the one procedure should become three rather than two.