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

• Microsoft SQL Server version 7.0

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

• Replace the variables with string constants.


-OR-


• Provide a hint to force a loop join. For example, use "INNER LOOP JOIN" instead of "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:

1. The query must contain an inner join.



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



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

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 

Last Reviewed: May 7, 1999