Re: many "or" operation make system choose incorrect index
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Mon, 26 Nov 2007 22:39:31 +0000 (UTC)
Gert-Jan Strik (sorry@xxxxxxxxxxxxxxxxxxxxx) writes:
Please note that there is a certain point at which the compilation time
grows a lot for each addition predicate you add to the WHERE clause. If
the compilation time exceeds the estimated gains, the optimizer will
stop compilation and simply choose a "good enough" plan.
Indeed. Many OR clauses, or many values in IN can result in horrendeous
compilation times. SQL 2005 fare a lot better than SQL 2000, but the cost
is still high.
One thing I've notice that when there are more than 63 values (I think
that was the value), SQL Server stashes all the constants into a work
table, and you get the same result as you had the values in a temp table.
At least that was what I saw in a test that I ran.
--
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
.
- Follow-Ups:
- References:
- many "or" operation make system choose incorrect index
- From: lsllcm
- Re: many "or" operation make system choose incorrect index
- From: Gert-Jan Strik
- many "or" operation make system choose incorrect index
- Prev by Date: Re: Running a DLL in the Query Analyzer
- Next by Date: Re: Create table and default order by clause
- Previous by thread: Re: many "or" operation make system choose incorrect index
- Next by thread: Re: many "or" operation make system choose incorrect index
- Index(es):
Relevant Pages
|
