December 16, 2009 at 1:09 pm
I'm supporting an application that uses a stored procedure to generate a Crystal Report. I did not write the sp, nor create the report. The report was timing out.
I decided to run the stored procedure directly in SSMS. It takes over 11 minutes to execute. However, when I cut the select statement into a new query pane and declare local variables for the parameters, the same results come back in 3 seconds.
Can someone please give me some ideas as to what would cause this and any possible solutions?
TIA
December 16, 2009 at 1:20 pm
Sounds like a classic case of parameter sniffing. It may benefit this stored proc to declare local variables and assign the values from the parameters to those local variables and using the local variables in the actual queries.
Here are a few good blob posts you may want to read.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/
December 16, 2009 at 1:46 pm
Lynn, you are a rock star! I'm not a sql/db expert, but I muddle through. I had never heard of this and had no clue what to do.
It worked like a charm.
December 16, 2009 at 2:17 pm
Glad I could help. But, please, take the time to read those blog posts as well. 😉
December 16, 2009 at 3:55 pm
Now that you know the symptoms, you would probably be amazed at how frequently this pops up.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply