Performance issue using conditional WHERE clause

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

Re: Performance issue using conditional WHERE clause

On Thu, 24 Jan 2008 20:11:21 -0800 (PST), Jared
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

Re: Performance issue using conditional WHERE clause

Jared wrote:

> 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.

I would at least try the following:

WHERE (@Name IS NULL OR [Name] = @Name)

as well as

WHERE NOT([Name] <> @Name)

Re: Performance issue using conditional WHERE clause

Jared (blacktoe.the.crippler@gmail.com) writes:
> 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.

SQL Server builds the query plan for the entire batch, and thus at
compile time the value of @Name is not known. Therefore the plan must
be such that it yields a correct result in either case.

> 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.

I have an article on my web site that discusses a number of possible
approaches to this problem, see http://www.sommarskog.se/dyn-search.html.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Performance issue using conditional WHERE clause

Try:
DECLARE @Name VARCHAR(32)
SET @Name = 'Bob'
SELECT * FROM Employees as e
INNER JOIN (SELECT id FROm Employees WHERE Name = @Name or Name IS NULL) t2
ON e.ID = t2.ID


Replacing ID with whatever your main Key is called


--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com




"Jared" wrote in message
news:d5382a71-4306-4603-809f-9082269c9a85@s8g2000prg.googleg roups.com...
> 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

Re: Performance issue using conditional WHERE clause

On Jan 24, 10:48=A0pm, Ed Murphy wrote:
> Jared wrote:
> > Consider the following two functionally identical example queries:
>
> > Query 1:
>
> > DECLARE @Name VARCHAR(32)
> > SET @Name =3D 'Bob'
> > SELECT * FROM Employees
> > WHERE [Name] =3D CASE WHEN @Name IS NULL THEN [Name] ELSE @Name END
>
> > Query 2:
>
> > SELECT * FROM Employees WHERE [Name] =3D '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. =A0However, Query 1 takes much longer to=

> > run on my indexed table of ~300,000 rows. =A0By "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? =A0I want to avoid using the IF...THEN
> > method because I frequently require several optional parameters in the
> > WHERE clause.
>
> I would at least try the following:
>
> WHERE (@Name IS NULL OR [Name] =3D @Name)
>
> as well as
>
> WHERE NOT([Name] <> @Name)- Hide quoted text -
>
> - Show quoted text -

Tom van Stiphout wrote:
> "I don't know why you would expect the QEP to be the same. I wouldn't."

I misspoke. I didn't mean that the QEP would be identical step for
step, but rather that the performance would be pretty much the same.


Ed Murphy wrote:
> I would at least try the following:
> WHERE (@Name IS NULL OR [Name] =3D @Name)

This gives me the same performance as hard-coded values, which is to
say it returns almost instantly. I'm not sure why using CASE results
in a longer execution time, but at this point I don't really care.
=3D)

Thanks for your help!

Re: Performance issue using conditional WHERE clause

SELECT * FROM Employees
WHERE [Name] = IsNull(@Name,[Name])

"Jared" a écrit dans le message de news:
d5382a71-4306-4603-809f-9082269c9a85@s8g2000prg.googlegroups .com...
> 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