Performance issue using conditional WHERE clause

#1

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

#2

On Thu, 24 Jan 2008 20:11:21 -0800 (PST), Jared
<blacktoe.the.crippler (AT) gmail (DOT) 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.

Quote:

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

#3

Jared wrote:

Quote:

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)

#4

Jared (blacktoe.the.crippler (AT) gmail (DOT) com) writes:

Quote:

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.

Quote:

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#5

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" <blacktoe.the.crippler (AT) gmail (DOT) com> wrote

Quote:

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

#6

On Jan 24, 10:48*pm, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:

Quote:

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)- Hide quoted text -

- Show quoted text -

Tom van Stiphout wrote:

Quote:

"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:

Quote:

I would at least try the following:
WHERE (@Name IS NULL OR [Name] = @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.
=)

Thanks for your help!

#7

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

"Jared" <blacktoe.the.crippler (AT) gmail (DOT) com> a écrit dans le message de news:
d5382a71-4306-4603-809f-9082269c9a85...oglegroups.com...

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Performance issue using conditional WHERE clause

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Performance issue using conditional WHERE clause - 01-24-2008 , 10:11 PM

Re: Performance issue using conditional WHERE clause - 01-25-2008 , 12:40 AM

Re: Performance issue using conditional WHERE clause - 01-25-2008 , 12:48 AM

Re: Performance issue using conditional WHERE clause - 01-25-2008 , 03:02 AM

Re: Performance issue using conditional WHERE clause - 01-25-2008 , 03:10 AM

Re: Performance issue using conditional WHERE clause - 01-25-2008 , 10:51 AM

Re: Performance issue using conditional WHERE clause - 01-28-2008 , 08:28 AM