SP taking 15 minutes, but the same query when exec

Question

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?

Answer 1

I would guess it could possible be down to caching. If you run the stored procedure twice is it faster the second time?

To investigate further you could run them both from management studio the stored procedure and the query version with the show query plan option turned on in management studio, then compare what area is taking longer in the stored procedure then when run as a query.

Alternativly you could post the stored procedure here for people to suggest optimizations.

Answer 2

I would suggest the issue is related to the type of temp table (the # prefix). This temp table holds the data for that database session. When you run it through your app the temp table is deleted and recreated.
You might find when running in SSMS it keeps the session data and updates the table instead of creating it. Hope that helps :)

Answer 3

I'd also look into parameter sniffing. Could be the proc needs to handle the parameters slighlty differently.

Answer 4

its due to parameter sniffing. first of all declare temporary variable and set the incoming variable value to temp variable and use temp variable in whole application here is an example below.

ALTER PROCEDURE [dbo].[Sp_GetAllCustomerRecords]
@customerId INT 
AS
declare @customerIdTemp INT
set @customerIdTemp = @customerId
BEGIN
SELECT * 
FROM   Customers e Where
CustomerId = @customerIdTemp 
End

try this approach

Answer 5

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.

Answer 6

Try recompiling the sproc to ditch any stored query plan

exec sp_recompile 'YourSproc'

Then run your sproc taking care to use sensible paramters.

Also compare the actual execution plans between the two methods of executing the query.

It might also be worth recomputing any statistics.