Script converted to stored procedure runs too long

Question

Script converted to stored procedure runs too long

chazmanian

Grasshopper

Points: 19
More actions
June 20, 2008 at 1:35 pm

#187387

I have four separate SQL statements. Each returns a resultset. When they are run as a group in a single script they all complete within 30 to 40 seconds.
I then put these in a stored procedure. Suddenly, they take 5 minutes to run to completion. There is virtually no difference between the script and the stored procedure except that a parameter is passed in. This parameter (in the script) was simply declared and set at the top of the script.
Can anyone offer some suggestion as to why this situation occurs?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Are you making use of SET NOCOUNT ON clause in case you don't need the rows affected statement? Try using it and see if it helps.

Manu

chazmanian Grasshopper Points: 19 More actions · Answer 2

Yes, I have that in there already. It is located right after the BEGIN and just before any of the SQL executes.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

Sounds like it could be parameter sniffing. Check the execution plans to see if they're changing between the non-parameratized values and the parameratized values. You can try a couple of tricks. Use the OPTIMIZE FOR query hint to get the execution plan to use a particular parameter value regardless of what you pass in. You can set two parameters on the query and give one a default value, again a value that generates a good plan, and then swap them out within the code. There are other tricks too. Do a search on parameter sniffing.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

chazmanian Grasshopper Points: 19 More actions · Answer 4

Thanks.

I "googled" parameter sniffing and found that some solutions had to do with creating local variables within the procedure that mirrored the parameters being passed in. That worked for my situation.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

Excellent. Thanks for reporting back what worked.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning