You are currently offline, waiting for your internet to reconnect

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

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.

Article ID: 919638 - Last Review: 01/17/2015 14:52:31 - Revision: 3.0

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

  • kbnosurvey kbarchive kbtshoot KB919638