Why is stored procedure slow, while same query fast?

Question

_{Tuesday, December 8, 2009 8:47 PM}

Does anyone have any idea why this is happening:

We have a stored procedure that is taking several minutes to execute (lots of decryptions of encrypted data; many records). In investigating it, I copied the script for the stored procedure to a new query window, substituted local variables for the parameters, and gave them the same values I was using for the SP.

I then ran it, and it ran in 4 seconds. But if I right-click on the stored procedure (in Management Studio) and select "run", it takes over 5 minutes to run!

EXACTLY the same code -- not one character changed. EXACTLY the same production data.

Why would this be happening, any ideas??

(It's SS 2005, btw)
CynthiaD

All replies (3)

_{Tuesday, December 8, 2009 8:53 PM ✅Answered | 4 votes}

The issue could be parameter sniffing. Check out this post by Plamen. Try adding the RECOMPILE hint to your stored procedure and see if you notice any improvement.

http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

Abdallah, PMP, ITIL, MCTS

_{Tuesday, December 8, 2009 10:09 PM}

It's the "parameter sniffing" -- never knew there was such a thing!

When I implement the solution in which you copy the parameters to local variables, the execution time of the stored procedure drops to a few seconds!

Who woulda thunk it -- thanks.CynthiaD

_{Friday, January 27, 2017 8:10 PM}

That's insane. I can't believe what a difference it made declaring local variables to avoid parameter sniffing. Thanks!