Posted by Jared on 01/25/08 04:11

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

• Next in forum: Re: Performance issue using conditional WHERE clause
• Prev in forum: Re: Connection Logs in SQL Server
• Thread view: Performance issue using conditional WHERE clause

[Reply to this message]