Re: Performance issue using conditional WHERE clause
- From: Jared <blacktoe.the.crippler@xxxxxxxxx>
- Date: Fri, 25 Jan 2008 08:51:39 -0800 (PST)
On Jan 24, 10:48 pm, Ed Murphy <emurph...@xxxxxxxxxxxx> wrote:
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!
.
- References:
- Performance issue using conditional WHERE clause
- From: Jared
- Re: Performance issue using conditional WHERE clause
- From: Ed Murphy
- Performance issue using conditional WHERE clause
- Prev by Date: Re: CALs and SQL Server
- Next by Date: Rmote database inserts blocking local queries.
- Previous by thread: Re: Performance issue using conditional WHERE clause
- Next by thread: Re: Performance issue using conditional WHERE clause
- Index(es):
Relevant Pages
|
