Microsoft KB Archive/919638

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

Article ID: 919638

Article Last Modified on 11/2/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition

-



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.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

Keywords: kbtshoot kbpubtypekc KB919638

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.