PRB: Inefficient Plan for BIT in Join Condition in WHERE Clause (281342)
The information in this article applies to:
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.
| Modification Type: | Major | Last Reviewed: | 11/6/2001 |
|---|
| Keywords: | kbprb KB281342 |
|---|
|