sql like query slow if using declare parameter but fast if not

Question

SQL 2008: This is slow (takes 1 1/2 minutes):

declare @p1 varchar(50)
set @p1 = '976j%'
select * from invsearch_query where comparepnfwd like @p1

This takes less than a second:

select * from invsearch_query where comparepnfwd like '976j%'

Why???

Take a look at the query execution plans (or post them here) — mellamokb
– mellamokb, Commented May 13, 2011 at 19:20
Try casting your literal to see if that makes any difference (CAST('976j%' AS VARCHAR(50))). Also, take a look at the execution plan to see the differences. — Jerad Rose
– Jerad Rose, Commented May 13, 2011 at 19:21
TAKES LESS THAN A SECOND: select * from invsearch_query where comparepnfwd like cast('976j%' as varchar(50)) — Rodney Burton
– Rodney Burton, Commented May 13, 2011 at 19:38
@Martin, yes you are right, the slow one was doing a clustered index scan on the main table (yes, invsearch_query was a view), and the fast one did index seeks and keylookups. — Rodney Burton
– Rodney Burton, Commented May 13, 2011 at 20:38

Martin Smith · Accepted Answer · 2011-05-13 19:26:21Z

10

I would imagine that you must have a non covering index with leading column comparepnfwd that is used by the literal query but not by the query with the variable.

You can use OPTION (RECOMPILE) to get SQL Server to recompile the plan taking into account the actual variable value.

answered May 13, 2011 at 19:26

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rodney Burton Over a year ago

option (recompile) made the 1st query as fast as the 2nd query. thanks.

Collectives™ on Stack Overflow

sql like query slow if using declare parameter but fast if not

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related