Microsoft KB Archive/308800

= FIX: Optimizer Chooses Poor Plan for a Query That Contains a Substring Function and a Comparison Operator =

Article ID: 308800

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q308800



BUG #: 354314 (SHILOH_BUGS)



SYMPTOMS
The optimizer may choose a poor plan for queries that contain:


 * A LIKE operator.

-and-


 * A string pattern that consists of a function evaluated on a column.

For example: USE pubs GO SELECT T1.au_id FROM authors T2, titleauthor T1 WHERE T2.au_id LIKE (SUBSTRING(T1.au_id, 5, 7) + '___')



CAUSE
The optimizer incorrectly estimates the cardinality of a LIKE predicate when the pattern is an expression that warrants a cardinality guess (for example, the SUBSTRING function), and the expression is evaluated over a column value.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack



WORKAROUND
To work around this problem, use either of these methods:   Create a temporary table to store the SUBSTRING function values, join to that table, and then use the temporary column in the LIKE comparison. For example: USE pubs GO SELECT (SUBSTRING(titleauthor.au_id, 5, 7) + '___') AS temp_au_id INTO #temp_titleauthor FROM titleauthor

SELECT #temp_titleauthor.temp_au_id FROM authors, #temp_titleauthor WHERE authors.au_id LIKE #temp_titleauthor.temp_au_id -or-

  Alter the table by adding a computed column based on the SUBSTRING function. For example: USE pubs GO ALTER TABLE titleauthor ADD sub AS (SUBSTRING(au_id, 5, 7) + '___')

SELECT T1.au_id FROM authors T2, titleauthor T1 WHERE T2.au_id LIKE T1.sub 



STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

Additional query words: performance LIKE plan inequality wildcard

Keywords: kbbug kbfix KB308800

-

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

© Microsoft Corporation. All rights reserved.