Posted by Erland Sommarskog on 11/26/07 22:39

Gert-Jan Strik (sorry@toomuchspamalready.nl) 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@sommarskog.se

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

• Next in forum: Re: Create table and default order by clause
• Prev in forum: Re: Running a DLL in the Query Analyzer
• Thread view: Re: many "or" operation make system choose incorrect index

[Reply to this message]