Microsoft KB Archive/281342

= PRB: Inefficient Plan for BIT in Join Condition in WHERE Clause =

Article ID: 281342

Article Last Modified on 11/6/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q281342



SYMPTOMS
When you use a column with a bit data type in the WHERE clause of a join condition in a query, the optimizer may choose an inefficient plan. The use of the inefficient plan results in performance degradation for the query.

The query is structured similar to the following: select Table1.col2 FROM   Table1 INNER JOIN Table2 ON    Table2.Col1 = Table1.Col1 WHERE (Table1.BitCol = 1) If the data type for column Table1.BitCol is bit, the query may experience performance degradation. If you change the data type for BitCol to binary or tinyint, the query performance improves.



CAUSE
Use SET SHOWPLAN_ALL to check the plan for the queries.

When you switch column Table1.BitCol to binary or tinyint, the plan uses a hash match or merge join.

If you switch the data type in the WHERE clause back to bit, nested loops are used again. The nested loops increase the number of scan counts and subsequently slow down the query.



RESOLUTION
Change the data type from bit to tinyint, binary or char(1), depending on your needs.

Additional query words: datatype data type

Keywords: kbprb kbpending KB281342

-

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

© Microsoft Corporation. All rights reserved.