FIX: A large array of IN clause parameters parses or compiles slower on SQL Server 2000 (296612)

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.

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.