Why does the parameterized version of this query run slower than a non-parameterized version?

Question

Sample Query:

CREATE PROCEDURE dbo.Test (@p varchar(10))
AS
DECLARE @param varchar(10)
SET @param = @p + '%'

SELECT * FROM table1 t1
INNER JOIN table2 t2 on t1.id = tr.id
WHERE t2.desc LIKE @param

I've a query which is similar to one above and when i use this in stored procedure it runs indefinitely without giving any output. But if i use same query as,

SELECT * FROM table1 t1
INNER JOIN table2 t2 on t1.id = tr.id
WHERE t2.desc LIKE 'A%'  -- notice no parameter here

This executes in less than a second.

My table2 has 140K records and table1 some 250K

Any idea what could be causing like operator to run slow?

Answer 1

It does not know at compile time that @param will not have a leading wildcard so when it compiles the batch it gives you a plan with a scan not a seek.

You could maybe try OPTION (RECOMPILE) or OPTION (FORCESEEK) (SQL Server 2008) to see if it gives you a better plan.