Re: Performance issue using conditional WHERE clause
- From: Tom van Stiphout <no.spam.tom7744@xxxxxxx>
- Date: Thu, 24 Jan 2008 23:40:26 -0700
On Thu, 24 Jan 2008 20:11:21 -0800 (PST), Jared
<blacktoe.the.crippler@xxxxxxxxx> 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
- References:
- Performance issue using conditional WHERE clause
- From: Jared
- Performance issue using conditional WHERE clause
- Prev by Date: Performance issue using conditional WHERE clause
- Next by Date: Re: Performance issue using conditional WHERE clause
- Previous by thread: Performance issue using conditional WHERE clause
- Next by thread: Re: Performance issue using conditional WHERE clause
- Index(es):
Relevant Pages
|
