I have a slow stored procedure like
CREATE PROCEDURE [dbo].[MyProc] ( @Param1 nvarchar(20), @Param2 nvarchar(20) ) AS BEGIN set nocount on ;with cte (col1, col2, col3) as ( select col1, col2, col3 from someTable --And the rest of it
I am trying to optimise it because it was taking too long.
When I call it as
exec MyProc @Param1=N'xxx', @Param2=N'yyy' it takes 20 seconds or so. To test what I could do I ran it like this:
DECLARE @Param1=N'xxx', @Param2=N'yyy' set nocount on ;with cte (col1, col2, col3) as ( select col1, col2, col3 from someTable --And the rest of it
and that takes about a second. Execution plans are completely different. Plan that is used when I call 'exec ...' is different to the plan that is used when I run the code directly. I re-indexed the tables used, I called
sp_recompile on the table and the stored procedure itself, I found the plans that are used for the SP and called
DBCC FREEPROCCACHE ( on the plan handles, but every time the plan is re-generated, they are different.
Any idea what's going on? Why would the same piece of code use different plans, one that takes 1 sec and the other taking 20 secs?