Re: Performance issue using conditional WHERE clause
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 25 Jan 2008 09:02:06 +0000 (UTC)
Jared (blacktoe.the.crippler@xxxxxxxxx) 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@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Performance issue using conditional WHERE clause
- From: Jared
- Performance issue using conditional WHERE clause
- Prev by Date: Re: Performance issue using conditional WHERE clause
- Next by Date: Re: Performance issue using conditional WHERE clause
- Previous by thread: Re: Performance issue using conditional WHERE clause
- Next by thread: Re: Performance issue using conditional WHERE clause
- Index(es):
Relevant Pages
|
