January 30, 2012 at 8:24 am
Hi,
I have an issue with SSRS 2008 where a report that should return many detail rows (> 1500 pages worth; not sure on exact count of rows, but I know it's > 1500 pages b/c that was returned when I ran the report using parameters I knew would return a smaller data set) is timing out. The report includes two summary tables, the extremely large data set and a smaller data set. The report is running 4 stored procedures.
The two summary tables and the large data set are essentially the same sp, but I did the calculations inside the SQL for the summary tables in the hopes of speeding up the execution (as opposed to doing them in the RDL).
Each sp runs w/in 10 minutes. I have removed any group bys, sorting and page breaks for the large data set. I am under the restraint of having a setting of a one hour run time on the server (congif files can't be updated by me). I have, however, checked the "do no timeout" box via the UI on the actual report.
In short, given that my sps run in a reasonable amount of time, does anyone have any advice in regards to updates to the rdl that will allow the report to execute in the alotted hour?
Any help is much appreciated.
Thanks,
Matt
January 30, 2012 at 8:42 am
Hi Matt,
Is the report run on demand or on a schedule?
Easiest way would be to strip out any & all formatting, just return the information in a basic table.
Or see if there's a way to split the report into smaller sections?
Alternatively, create the report in a file using SSIS, bypassing SSRS altogether (I suspect that's not an option though!)
Cheers
January 30, 2012 at 8:54 am
Hi,
Yes, the report is run on demand around the beginning of each month by an end user on the Report Manager. I've tried emailing it to myself and tried a file share. None of which has worked.
It's really a basic table. No formatting whatsoever. Just a straight dump of the data.
I think the best way to run it would be running the sp via SSIS and just dumping the file to a shared folder. It's just really inefficient when the end user should be able to get all her data from the same report.
I scoured the Internet for tips and tricks on this issue. There doesn't seem to be a firm solution to large data sets in SSRS, especially when we're dealing with millions of rows and the prod reporting environment must have a time limit.
Thanks,
Matt
January 30, 2012 at 10:16 am
Hi Matt,
You could try running it on a schedule if it's required on a known date each month.
This should take the rendering part out of the execution time completely.
(Best would be overnight if you're not 24/7 - you might also get the ok to increase the timeout value if this is the case).
If you look in the ExecutionLog view in ReportServer database this will also break down the run into data retrieval, processing and rendering times, to confirm it's the rendering that's the problem.
Otherwise you're probably looking at the SSIS solution.
I don't think SSRS is really designed for this kind of report. Imagine trying to consume a 1500+ page on the SSRS website itself!
I always wonder exactly how much use these size reports are to people (having written a few myself!) 😀
Cheers
Gaz
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply