IF / ELSE Execution in Stored Procedures
I found this article explaining the use of IF / ELSE statements in a SP can cause performance degradation using separate SPs for each branch. http://sqlmag.com/t-sql/if-statements-and-stored-procedure-performance
But I have an SP that selects the same columns from the same tables, and only the WHERE clause changes depending on which variables are present. Here's 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 separate SPs to handle different variables, or is it okay to have it all in one? (I know using is SELECT *
not good practice. I just did it for the sake of example)
source to share
Normally I wouldn't bother with this, although you should look at the whitepaper that Mikael Eriksson refers to, which contains a lot of useful information on the subject. However, I would remove the operator Column1 = Column1
in the branch else
because it could potentially confuse the optimizer.
This article addresses the fact that the stored procedure is compiled on first run. This can have perverse results. For example, if the table was empty the first time it was called, then the optimizer might prefer a full table scan to find the index, and this will be bad as the table gets bigger.
The problem might be that one of the branches is getting a suboptimal performance plan because the data is not typical for the first call. This is especially true if one of the values is NULL
. This happens not only with if
, but this is the case when you need to be sensitive to the problem.
I would recommend the following:
- If your tables grow / shrink over time, recompile the stored procedures periodically.
- If your tables are representative of the data, don't worry about splitting up multiple stored procedures.
- Your examples should do index searches, which is pretty straightforward. But monitor performance and check execution plans to make sure you need them.
- You can use hints if you want to enforce the index. (Personally, I need hints to enforce ad hoc join algorithms, but not using an index, but I'm sure someone else had a different experience.)
For your examples, an index on table1(column1)
and is sufficient table1(column2)
.
The summary of recommendations is not to fix this until you see there is a problem. Putting logic in the two stored procedures should be about fixing a problem that you actually see, not anticipating a problem that can never exist. If you go with a two-step approach, you can still have one interface that each calls, so you still have the same API. In other words, one procedure should become three, not two.
source to share