Article ID: 296612
Article Last Modified on 4/12/2004
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q296612
BUG #: 353368 (SHILOH_BUGS)
SYMPTOMS
After an upgrade from SQL Server 7.0 to SQL Server 2000, a SELECT statement that includes an array of 50 or more parameters associated with an IN clause may take longer to compile and parse. The increase in time is directly proportional to the increase in the number of parameters.
CAUSE
Because SQL Server 2000 uses larger histograms when gathering statistics on a table, estimates of the cardinality of a query for determining the best query plan can take longer.
RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
Hotfix
NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 2.
The English version of this fix should have the following file attributes or later:
File name
-----------
s80281i
Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.
WORKAROUND
If the queries are populating the IN clause based on the content of other tables, adding another JOIN statement to the query can increase performance by using existing indexes and accessing the data through the storage engine instead of iterating through the query plan.
If the parameters are populated by user input, serial devices, or other client caching, inserting them into a temporary table and adding a JOIN or correlated subquery will increase performance as well.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.
MORE INFORMATION
Queries that use the IN clause are converted into a series of queries using OR statements. The optimizer calculates the estimation of cardinality and looks for a long continguous sequence where expressions are all column=constant, such as (a=1 or a=10 or a=11, ...). For each such sequence of 20 or more expressions, a separate cardinality estimation is prepared.
This is a compile-time fix that will not address all performance issues involving large IN clauses. For instance, if there is a CONVERT on the column being compared to the IN list, it will not be improved by this fix because that is not a compile-time issue.
Keywords: kbbug kbfix KB296612
