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.
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: September 17, 2011 - Revision: 2.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: 
kbtshoot KB919638

Give Feedback

 

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