December 8, 2009 at 1:31 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.
Why would this be happening, any ideas??
Cynthia
December 8, 2009 at 2:03 pm
Sounds like parameter sniffing. If you do a google search on this site you should find several threads that discuss the topic.
December 8, 2009 at 2:28 pm
Oh, you are right! How totally strange!
When I do what was suggested -- copying the parameters to local variables and using the local variables in the select statement -- the SP runs in a few seconds also!
Wow, you would think Microsoft would fix this -- does "parameter sniffing" ever serve any useful purpose?
Thanks very much!
Cynthia
December 8, 2009 at 4:34 pm
OMG!!! Thank you! Thank you! Thank you!!
I have been fighting this exact problem for about 3 weeks now, just never found the right key words to search with. Lucky accident led me to this post. I googled 'parameter sniffing', implemented the suggestion for using local variables, and my stored proc went from 40 minutes execution time to just over 1 minute - consistently!
Woo hoo, happy dance in the cube!!! You guys ROCK!:-D
Lori
December 8, 2009 at 9:03 pm
Glad to have been able to help both of you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply