FIX: Parameters Not Pushed to Subqueries May Cause Suboptimal Plan (320506)

SYMPTOMS

SQL Server may select a slow query plan for certain queries that incorporate elements similar to those in the following query:

SELECT * FROM table1 
WHERE column1 = X 
  AND EXISTS (SELECT * FROM table2 WHERE table2.column1 = table1.column1)

The relevant elements of this query are:

The query's WHERE clause includes an equality predicate that references a single column (for example, "column1 = X", where X can be either a literal or a variable).
The query includes an EXISTS or NOT EXISTS clause (or some other construct that is implemented as a semi-join or anti-semi join) with a subquery that is associated with the outer query through an equi-join on the same column.

In some cases, the less accurate cardinality estimate may cause SQL Server to generate a suboptimal plan.

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

NOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.

The English version of this fix should have the following file attributes or later:

   File name        Version      Description
   ------------------------------------------------

   Sqlservr.exe     8.00.608     SP2-based hotfix

NOTE: Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.

WORKAROUND

To work around this behavior, modify the query and make the implied filter condition for the subquery explicit. For the query example in the "Symptoms" section, here is how you can modify the query to get a better plan:

SELECT * FROM table1 
WHERE column1 = X 
  AND EXISTS (SELECT * FROM table2 WHERE table2.column1 = X)

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

MORE INFORMATION

For the query example in the "Symptoms" section, you can replace the join condition "table2.column1 = table1.column1" with the simpler predicate "table2.column1 = X", because the subquery performs an equi-join on column1 and the outer query restricts the qualifying rows in table1 to those where column1 = X. The simpler predicate lets the optimizer make a more accurate cardinality estimate for this portion of the query plan. This build of SQL Server extends support for this simplification technique to anti-semijoins.