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

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