July 30, 2009 at 8:01 am
Hi,
I just finished doing a performance update on a store procedure that i wrote for a report. I pulled the code out of the stored procedure and did my rewrite in a new query window (Declaring my variables and setting them to take place of passing parameters). I got the execution time down to 3 seconds which is perfect for what i wanted (Also a big change from over a minute). The weird part is when I put the script back into a sp and execute the stored procedure the execution time takes over 2 minutes and 40 seconds. I have absolutely no idea why this would happen. Has anyone ever had this happen to them before?
Thanks,
Nick
July 30, 2009 at 8:17 am
Sounds like a classic case of parameter sniffing. Do a search on that term. There are multiple ways to fix the issue.
"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
July 30, 2009 at 9:41 am
Thanks for the info and yea I did find the person on this forum who had the exact same problem as me and I can't believe that the solution is that stupid/simple but it works.
Thanks,
Nick
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply