Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cedubose
Starting Member
22 Posts |
 Posted - 2009-12-08 : 15:39:08
|
| 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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
cedubose
Starting Member
22 Posts |
Posted - 2009-12-08 : 17:12:58
|
| It turns out it's the "parameter sniffing" thing. Strange -- I never knew there was such a thing. When I implement the fix whereby you copy the parameters to local variables, the execution time of the stored procedure drops to a few seconds.Cynthia |
 |
|
|
|
|
|
Very slow SP, but query is very quick