Microsoft KB Archive/269883

= BUG: SQL Server Optimizer Chooses Bad Plan with Self Joined Tables =

Article ID: 269883

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q269883



BUG #: 57365

Bug #: 235842



SYMPTOMS
If a query joins two tables where the second table performs self joins, the SQL Server query optimizer may fail to pick an optimal plan.



CAUSE
Without optimizer hints, SQL Server uses a hash match root/inner join query plan that is exponentially slower than using a nested loop/inner join plan.



WORKAROUND
To work around this behavior:  Change the SET FORCEPLAN setting to ON. Using the SET FORCEPLAN ON setting forces the query optimizer to join the tables in the same order that they appear in the SELECT statement.

-or-

  Specify the join type. For example: SELECT [column list] FROM [tableA] INNER LOOP JOIN [tableB] ON [condition] 



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.

Keywords: kbbug kbpending KB269883

-

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

© Microsoft Corporation. All rights reserved.