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

Article translations Article translations
Article ID: 919638 - View products that this article applies to.
This article has been archived. It is offered "as is" and will no longer be updated.
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

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 "Applies to" section.

Properties

Article ID: 919638 - Last Review: January 17, 2015 - Revision: 3.0
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
Keywords: 
kbnosurvey kbarchive kbtshoot KB919638

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com