Query performance may decrease when you try to run a query in a parallel plan in SQL Server 2000 (919638)

SYMPTOMS

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) o
WHERE o.my_id in ( SELECT int_value IN Table_TMP )

Notes

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.

CAUSE

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.

Query performance may decrease when you try to run a query in a parallel plan in SQL Server 2000 (919638)

SYMPTOMS

CAUSE

STATUS