Sql query is slow if declare parameter is used, 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

It takes less than a second:

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

Why???

Answer 1

I would suggest that you should have an uncoated index with a comparepnfwd leading column, which is used by a literal query, but not by a query with a variable.

You can use OPTION (RECOMPILE) to force SQL Server to recompile the plan based on the actual value of the variable.