Stored Proc MUCH slower when called from SSRS
Hi here, I have a sproc that when I call within Management Studio it runs in about 25 seconds. When I call that same sproc from SSRS, it takes about 4 minutes.
I have used Profiler to watch the sproc being called from both places and the call is identical..I have even copied the exact call with params from profiler and pasted it into management studio and what takes 4 minutes to run from SSRS takes 30 seconds in
MS.
Please keep in mind, I'm not talking about *rendering* time here, I am talking when the sp:Started to sp:Completed. The rendering on the long one doesn't even happen...it just sits there on 'loading ..'.
Any ideas? This is kind of a deal breaker for my situation. If we can't get performance to equal that of just a straight query, we won't be using SSRS.
Jen Campion
Jen Campion
February 17th, 2011 5:35pm
Have a look at this link :
http://social.technet.microsoft.com/Forums/en/sqlreportingservices/thread/762c54ed-e631-42a5-a89d-5e033ebc26dd
Free Windows Admin Tool Kit Click here and download it now
February 18th, 2011 5:11am
Hi Jen Campion,
You can use the report execution log to find out how many milliseconds of processing time is spent on each processing phase, besides this, please make sure the network status is normal and do a test to other report to see whether it works
fine.
For more information about Report Server Execution Log, please see:
http://msdn.microsoft.com/en-us/library/ms159110.aspx Challen Fu [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
February 20th, 2011 9:25pm
SQL Server MVP Erland Sommarskog published an article on this subject today. See
http://www.sommarskog.se/query-plan-mysteries.html for a discussion of possible causes.
Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
Free Windows Admin Tool Kit Click here and download it now
February 20th, 2011 10:05pm
Thanks for all the replies...I'm going to plug away at these and see if I can figure out the problem. Thanks! Ugh...
Jen
Jen Campion
February 25th, 2011 11:50am