Menu
So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a 'running total' into a third temp table which is created using CREATE. Then this resulting temp table is joined with other tables in the DB to generated the result after some grouping etc. The problem is that this SP had been running fine until now returning results in 1-2 minutes. And now suddenly its taking 12-15 minutes. If I extract the query from the SP and executed it in the management studio by manually setting the same parameters it returns results in 1-2 minutes but the SP takes very long. Any idea what could be happening. I tried to generate the Actual Execution plans of both the Query and the SP but it couldn't generate it because of the cursor. Any idea why the SP takes so long while the query doesn't?
233
Either you are getting different performance because the system is under a different sort of load, you are getting different performance because of data volume changes, or you are getting different performance because you are getting different query plans.
Stored procedures are precompiled and optimised, which means that the query engine can execute them more rapidly. By contrast, queries in code must be parsed, compiled, and optimised at runtime.
When a query hits your server, a plan has to be compiled. To save time and resources later, an execution plan is cached based on the estimated rows that parameter will cause your code to process and return.
This is the footprint of parameter-sniffing. See here for another discussion about it; SQL poor stored procedure execution plan performance - parameter sniffing
There are several possible fixes, including adding WITH RECOMPILE to your stored procedure which works about half the time.
The recommended fix for most situations (though it depends on the structure of your query and sproc) is to NOT use your parameters directly in your queries, but rather store them into local variables and then use those variables in your queries.
168
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
