Microsoft KB Archive/261846

= BUG: Filter Plan Chosen Instead of Selective Clustered Index Seek for LIKE or IN That Contains Predicates =

Article ID: 261846

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q261846



BUG #: 57777 (SQLBUG_70)



SYMPTOMS
The Microsoft SQL Server optimizer might incorrectly conclude that a plan, which uses a query FILTER, will perform better for a predicate that contains a LIKE operator or an OR clause than a clustered index SEEK on a very selective column.



WORKAROUND
To work around this behavior, you can use the following query structure: For queries that contain the LIKE operator: SELECT * FROM TblA WHERE A = '100' AND (B > '60' AND B < '61') GO

For queries that contain the OR operator: SELECT * FROM TblA WHERE (A = '100' OR A='200') AND (B > '60' AND B < '61') GO



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



MORE INFORMATION
In such a situation, the filter is applied to a less specific index SEEK. For large tables this can lead to performance degradation because the rows are placed in cache before they are filtered. What you expect is a Clustered Index Seek to be chosen against the columns with the increased selectivity.

The following script demonstrates the problem: CREATE TABLE TblA ( A  VARCHAR(3)  NOT NULL, B   VARCHAR(100)    NOT NULL, C   VARCHAR(100)    NOT NULL ) GO CREATE UNIQUE CLUSTERED INDEX ucl_TblA ON TblA(A, B) GO SET NOCOUNT ON GO DECLARE @x INT SELECT @x = 0 WHILE @x < 1000 BEGIN SELECT @x = @x +1 INSERT TblA (A, B, C) VALUES('100', CONVERT(VARCHAR(100), @x), CONVERT(VARCHAR(100), @x)) END WHILE @x < 2000 BEGIN SELECT @x = @x +1 INSERT TblA (A, B, C) VALUES('200', CONVERT(VARCHAR(100), @x), CONVERT(VARCHAR(100), @x)) END GO

UPDATE STATISTICS TblA GO print &quot;&quot; print &quot;&quot; print &quot;PERFORM Poorly (notice the Filter)&quot; print &quot;&quot; GO SET SHOWPLAN_TEXT ON GO SELECT * FROM TblA WHERE A = '100' AND B LIKE '60%' GO SELECT * FROM TblA WHERE (A = '100' AND B > '60' AND B < '61') OR (A = '200' AND B > '60' AND B < '61') GO SET SHOWPLAN_TEXT OFF GO print &quot;&quot; print &quot;&quot; print &quot;PERFORM WELL&quot; print &quot;&quot; GO SET SHOWPLAN_TEXT ON GO SELECT * FROM TblA WHERE A = '100' AND (B > '60' AND B < '61') GO SELECT * FROM TblA WHERE (A = '100' OR A='200') AND (B > '60' AND B < '61') GO SET SHOWPLAN_TEXT OFF GO The poorly performing queries generate SHOWPLAN texts (on SQL Server 7.0, SP1, and SP2) similar to the following: StmtText |--Filter(WHERE:(like([TblA].[B], '60%'))) |--Clustered Index Seek(OBJECT:([pubs].[dbo].[TblA].[ucl_TblA]), SEEK:([TblA].[A]='100' AND [TblA].[B] >= '60' AND [TblA].[B] < '61') ORDERED) In this example the statistics indicate the following: Density should encourage the SEEK on both columns (that is, A & B): Statistics for INDEX 'ucl_TblA'. Updated             Rows  Rows Samp Steps Density  Average key length - - -  May 12 2000  2:09PM  2000  2000      286   0.0      0.0

All density  Columns - -- 0.5          A 5.0000002E-4  A, B NOTE: Relocating the clustered index does not seem to improve the symptoms.

Keywords: kbbug kbpending KB261846

-

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

© Microsoft Corporation. All rights reserved.