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?
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.