"IF..ElseIf..Else" or "Where clause" to guide Stored Procedure results

Question

I have the following two SQL statements

First one:

IF(@User_Id IS NULL)  
BEGIN  
      SELECT *
      FROM [UserTable]
END  
ELSE           
BEGIN                  
      SELECT *
   FROM  [UserTable] AS u
   WHERE  u.[Id] = @User_Id                  
END

Second one:

SELECT  *
FROM [UserTable] AS u
WHERE (@User_Id IS NULL OR u.[Id] = @User_Id)

Both of those queries would be wrapped in its own stored procedure. I am suspecting that the IF statement is causing a lot of recompilations on SQL. I am faced with either separating each part of the IF statement into its own stored procedure, OR replacing the entire IF statement with a WHERE clause (illustrated above in the second SQL statement)

My question is: What is the difference between the two statements from a performance perspective, and how would SQL treat each statement?

Thanks.

If you are using SQL Server 2005+, why not look at the Execution Plan to see what the difference is? — Neil Knight, Nov 04 '10 at 09:55
Also, you don't need to retest if the @User_Id IS NOT NULL in your ELSE statement as you know it is not NULL. — Neil Knight, Nov 04 '10 at 09:56
@Ardman: Yes I am, I have looked at the Execution Plan. The IF Statement performs better. But doesnt solve the problem of recompilations of the query plan. — MikeStichling, Nov 04 '10 at 10:15
@MikeStichling, have you tried replacing `(@User_Id IS NULL OR u.[Id] = @User_Id)` with `u.[Id] = COALESCE(@User_Id),u.[Id])` and then comparing the execution plan? — , Nov 04 '10 at 13:27
@Mark Bannister: Thanks, I have tried what you suggested and from what I can see when comparing the Execution plans between @User_Id IS NULL and COALESCE, they essentially translate to the same thing. As running the query with SET STATISTICS IO ON gives exactly the same stats. — MikeStichling, Nov 04 '10 at 13:58

score 0 · Accepted Answer · answered Nov 04 '10 at 18:05

Both solution will generate identical number of compilations.

The first solution the query optimizer is free to come up with the best plan for each of the two, different, queries. The first query (on the NULL branch of the IF) is not much that can be optimized, but the second one (on the NOT NULL branch of the ID) can be optimized if an index on Id column exists.

But the second solution is an optimization disaster. No matter the value of the @User_Id parameter, the optimizer has to come up with a plan that works for any value of the parameter. As such, no matter the value of @User_Id, the plan will always use the suboptimal table scan. There is just no way around this issue, and this is not parameter sniffing as some might think. Is just correctness of the plan, even if the value at plan generation time is NOT NULL, the plan has to work even when the parameter is NULL, so it cannot use the index on Id.

Always, always, always, use the first form with the explicit IF.

Thanks, im a bit confused though as to how SQL determines if a Stored Procedure needs to be recompiled. One of the criteria is a change in statistics. The two different branches in the IF Statement, would return very different stats based on parameter value. Would that not cause the stored procedure to be marked for recompilation? — MikeStichling, Nov 04 '10 at 21:51
The statistics are a property of an object (a table), not of a query. So the two queries will return different *results*, but the plan will be compiled based on the same stats (the stats of `UserTable`). If your queries are for real like the one sin your post, then you need not worry about recompilation since both queries are what si called 'trivial queries' which have a very very low cost of compilation (ie. the plans are 'obvious'). Even if recompilations would occur, most times a bad plan (like option 2 would create) is way more damaging than a recompilation. — Remus Rusanu, Nov 04 '10 at 22:27

"IF..ElseIf..Else" or "Where clause" to guide Stored Procedure results

1 Answers1