This article has been archived. It is offered "as is" and will no longer be updated.
Consider the following scenario. You try to run a query in Microsoft SQL Server 2000. The query runs in a parallel plan. In this scenario, query performance may decrease.
Query performance may also decrease if the following conditions are true:
One table that is involved in the query contains many rows. However, the other table contains few rows.
The query joins one relation with a second relation to form a derived table or a view.
The second relation contains one of the following:
The UNION operator and a NOT EXISTS clause
The NOT IN operator
For example, the query structure may resemble the following:
SELECT o.*FROM ( Part A UNION ALL Part B) oWHERE o.my_id in ( SELECT int_value IN Table_TMP )
The Table_TMP table contains only a few records.
The joins are on columns that are of the int data type.
The statistics have been updated.
There are no hypothetical indexes.
This problem may occur when SQL Server reorders a join in a derived table or in a view. If SQL Server reorders the join so that the join is performed after the UNION operation, the resulting query tree may have a project operator between the union and an anti-semijoin operator. This project operator implements the NOT IN operator or the NOT EXISTS clause.
In this scenario, SQL Server 2000 will not reorder the anti-semijoin around the intermediate project to allow for additional join reordering of the remaining tree. Therefore, query performance may decrease.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.