Posted by Tom van Stiphout on 01/25/08 06:40

On Thu, 24 Jan 2008 20:11:21 -0800 (PST), Jared
<blacktoe.the.crippler@gmail.com> wrote:

I don't know why you would expect the QEP to be the same. I wouldn't.

It would be interesting to see if COALESCE would cause the same
performance bottleneck. Can you try that on your system?
-Tom.


>Consider the following two functionally identical example queries:
>
>Query 1:
>
>DECLARE @Name VARCHAR(32)
>SET @Name = 'Bob'
>SELECT * FROM Employees
>WHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name END
>
>Query 2:
>
>SELECT * FROM Employees WHERE [Name] = 'Bob'
>
>I would expect SQL Server to construct an identical QEP under the hood
>for these two queries, and that they would require essentially the
>same amount of time to execute. However, Query 1 takes much longer to
>run on my indexed table of ~300,000 rows. By "longer", I mean that
>Query 1 takes about two seconds, while Query 2 returns almost
>instantly.
>
>Is there a way to implement a conditional WHERE clause without
>suffering this performance hit? I want to avoid using the IF...THEN
>method because I frequently require several optional parameters in the
>WHERE clause.
>
>Thanks!
>Jared

[Back to original message]