Thursday, February 2, 2012

Fast Query but Slow Stored Procedure

Few days ago we had a problem that I encountered years ago while working in Italy.
Basically a Stored Procedure is very slow. So slow that we didn't wait for it to finish, it probably more than 30 minutes. Let's not forget to mention that I'm running it on SQLServer 2008.
That query taken outside the stored procedure an run with the same parameters in SQLServer Management Studio runs in only few seconds. Since back in Milan I saw the same behaviour (on SQLServer 2005 that time) I thought I have the answer and can impress the colleagues with a fast solution: using  WITH RECOMPILE clause.
However, the result was the same...very slow stored procedure.
After searching the Internet we found the answer here. Parameters sniffing.  Explained here as "the process of using the parameter value to estimate selectivity and cardinality".
Indeed, our query is pretty complex and it looks like SQLServer did not know to re-create the execution plan. After changing the parameters' names, everything was OK.