BUG: Poor Performance with Inner Join with Multiple LIKE Clauses Using Variables

ID: Q225093

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 55032 (SQLBUG_70)

SYMPTOMS

An inner join between two tables with multiple LIKE clauses produces a poor query plan if the LIKE clauses use variables instead of string literals.

WORKAROUND

To work around this use one of the following:

Replace the variables with string constants.

-or-

Provide a hint to force a loop join. For example, use an INNER LOOP JOIN instead of an INNER JOIN.

STATUS

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

MORE INFORMATION

The following conditions must be met to encounter this bug:

The query must contain an inner join.

The WHERE clause of the query must contain multiple LIKE predicates.

The LIKE predicates must be passed a variable, not a string constant (that is, "... AND column LIKE @myvar1 ...").

The inner join may use either ANSI-standard or Transact-SQL (TSQL) join syntax.

For example, the following query demonstrates this problem:


SELECT p.first_name
FROM person p
INNER JOIN customer c ON p.person_id = c.person_id 
WHERE p.last_name LIKE @last_name AND
   p.first_name LIKE @first_name

Additional query words:

Keywords : SSrvTran_SQL kbbug7.00
Version : winnt:7.0
Platform : winnt
Issue type : kbbug