Clever Geek Handbook

Optional parameters in stored procedures - CASE or OR

Is there a performance difference between these filters?

Method 1: WHERE (@Col1 IS NULL OR t.column = @Col1) 


Method 2: WHERE 1 = case when @col1 is null then 1 else case when col1 = @col1 then 1 else 0 end end
+2


source to share


3 answers


If you know that your Col1 does not contain any null values, you can do this:

WHERE Col1 = COALESCE(@Col1, Col1)


Otherwise, your CASE statement should usually be slightly better than OR. I add an emphasis on "typical" because the table is always different. You should always know the profile to know for sure.

Unfortunately, usually the fastest way is to use dynamic sql to exclude the condition from the query in the first place if the parameter is null. But of course, keep this as an optimization of last resort.

+2


source


Why not use Coalesce?

Where Col1 = Coalesce(@Col1, Col1)


EDIT: (thanks to Joel comment below) This only works if col1 does not allow Nulls, or if it allows NULLs and you want null values ​​to be excluded when @ Col1 is null or not present. SO, if it allows nulls and you want them to be included when @ Col1 is null or missing, change as follows:

 Where Coalesce(@Col1, Col1) Is Null Or Col1 = Coalesce(@Col1, Col1)
+2


source


Yes. CASE has a guaranteed order of execution, while OR does not. Many programmers rely on OR short-circuiting and are surprised to learn that a set of oriented declarative languages ​​like SQL does not guarantee short-circuiting a boolean operator.

It is bad practice to use OR and CASe in WHERE clauses. Divide this condition into a clear IF statement and get separate queries for each branch:

IF @col1 IS NOT NULL
  SELECT ... WHERE col1 = @col1;
ELSE
  SELECT ... WHERE <alternatecondition>;

Placing a clause inside a WHERE usually overwhelms the optimizer who has no idea what @ col1 is and creates a bad plan that includes a full scan.

Update

Since I'm tired of explaining over and over again that logical short circuiting is not guaranteed in SQL, I decided to write a full blog column about this: Encoding SQL Server Statement Short Circuit . There you will find a simple counter example showing that not only is a boolean short circuit not guaranteed , but relying on it can be very dangerous as it can lead to runtime errors .

+2


source






More articles:


All Articles