March 20, 2012 at 12:54 pm
Hello Folks,
I have been dealing with a performance issue with my reports where the store procedures that produce the report results run extremely fast from the data warehouse when ran from Management Studio or from extracted from Excel (for example), but when I either try to preview the report in designer or export it to the report manager and run it from there, it takes a long time to show the result dataset. I have tried different browsers and no difference. The database and the SSRS manager are located on two different servers that are powered by extremely powerful hardware which indicates that is not a hardware problem.
I looked at the profiler / execution plan and everything seems ok as mentioned before when ran from management studio they are lighting fast but when using the report manager it is extremely slow for a small amount of records being returned for some of the reports (~2000 records) that could take up to 5-10 mins to come back.
Any ideas are much appreciated.
Thanks!
R.
March 20, 2012 at 1:07 pm
do you have a lot of defaults for the parameters, for example @datefrom dateime = NULL in your definitions?
it might be because of parameter sniffing exactly because of the defaults...the compiler might make a paln that assumes the defautl value is the best plan, when you really are including the defaults for convenience.
first thing i'd try is adding the OPTIMIZE FOR UNKNOWN on your procs and see if that helps.
Lowell
March 20, 2012 at 1:10 pm
I actually dont have any defaults for the 4 parameters I have, however each parameter is dependent from the one prior, meaning that I load the second parameter based on what they chose on the first one, the third one based on first and second and a default (today) date parameter as fourth. I also allow for multiple select on all of them.
I will look into OPTIMIZE FOR UNKNOWN.
Thanks!
R.
March 20, 2012 at 1:19 pm
Also, as mentioned before, this only occurs when using the designer preview or any other browser through the report manager in ssrs after deployment. When i physically run the SQL execute SP query and hard code the same values that I am sending through the browser, the store procedure comes back with results in less than a second.
March 20, 2012 at 1:55 pm
I found this blog post that does a pretty good job of providing tips to improve your reports. As mentioned, there are 3 components that impact report performance: data retrieval, report processing, and rendering.
This information is available on the report server DB and can help shed a light on where the bottleneck is stemming from.
http://www.keepitsimpleandfast.com/2011/07/more-tips-to-improve-performance-of.html
Here's another link from MSDN that also provides a few tips on building reports for scalability and performance.
http://msdn.microsoft.com/en-us/library/bb395166(v=sql.90).aspx#rptdsgntps_topic2
You may also want to define page breaks in your resulting tableset especially if you're bringing back a large number of results. Page-breaks can help "hide" much of the processing from the user as the rest of the pages are being rendered in the background. Report-level filters rather than query parameters can also save some time in gathering just the data that you need. And as mentioned in the MSDN link, enabling drilldowns around detail row-items could help avoid returning large reports.
March 20, 2012 at 2:42 pm
Found this solution on this site which i applied and worked like a charm! Thanks so much for ALL your help!
LINK TO SOLUTION : http://qa.sqlservercentral.com/Forums/Topic1025852-150-2.aspx
Thanks again!
Renatto.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply