By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,678 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,678 IT Pros & Developers. It's quick & easy.

Performance issue using conditional WHERE clause

P: n/a
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
Jan 25 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Thu, 24 Jan 2008 20:11:21 -0800 (PST), Jared
<bl*******************@gmail.comwrote:

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
Jan 25 '08 #2

P: n/a
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)
Jan 25 '08 #3

P: n/a
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" <bl*******************@gmail.comwrote in message
news:d5**********************************@s8g2000p rg.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

Jan 25 '08 #4

P: n/a
Jared (bl*******************@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, es****@sommarskog.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
Jan 25 '08 #5

P: n/a
On Jan 24, 10:48*pm, Ed Murphy <emurph...@socal.rr.comwrote:
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:
"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] = @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!
Jan 25 '08 #6

P: n/a
SELECT * FROM Employees
WHERE [Name] = IsNull(@Name,[Name])

"Jared" <bl*******************@gmail.coma écrit dans le message de news:
d5**********************************...oglegroups.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

Jan 28 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.