BUG: SQL Server Optimizer Chooses Bad Plan with Self Joined Tables (269883)

The information in this article applies to:

Microsoft SQL Server 7.0
Microsoft SQL Server 2000 (all editions)

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.

Modification Type:	Major	Last Reviewed:	10/17/2003
Keywords:	kbBug kbpending KB269883