March 1, 2011 at 2:58 pm
I have a fairly simple reporting services report that displays the results of a stored procedure.
The procedure, while fairy complicated, returns in less than a second in SSMS.
Whe I create the report in Visual Studio, and attempt to run it, it takes three to four minutes to run.
Same peoceedure, same parameters, very different performance.
I'm rusty, at best, with reporting services.
Thanks!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 2, 2011 at 12:44 pm
are you running them on the same database server or is one test and the other production?
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 2, 2011 at 2:44 pm
Same server, same database, same everything.
We tweaked some of the things in the report, such as sizing all of the text boxes properly and turning off CanGrow and CanShrink, and the performance improved.
It seems as if SSRS 2005 does a lot of extra processing to render the report.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 2, 2011 at 2:58 pm
Parameter sniffing is a common cause for very slow SSRS reports when the dataset is populated by a stored procedure. Basically to avoid this you need to declare local variables inside the SP and then set them to equal the parameters, then use the local variables in the rest of the stored procedure. You can search this site or Google for parameter sniffing and probably find everything you need.
One other thing to do after you have looked into Parameter sniffing is to look at the view in the reporting services database called ExecutionLog2. Look at the columns TimeDataRetrieval, TimeProcessing, and TimeRendering. It will give you an indication of where things are bogging down.
Hope this helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply