I've got a complex report with many subreports that is running on a reporting services 2005 machine with sp2 installed. The report was working well, producing a 2800 page report in under 10 minutes. I made a change to an expression in a table's column header and one change to the detail and the report stopped working. It would just hang for hours with no messages in the logs.
I tried to simplify what the report server had to do by removing the sum's to the database side's stored proc but that didn't work. I finally managed to get the report to work by removing a totals section to a subreport. I've also had the same hanging condition occur when I merged some columns in the table. I also found a workaround for this problem.
Is anyone else finding problems like this? They don't seem to be code related.
Is there a limit to how complex a report can be in reporting services or the PDF rendering part of the process?
Thanks for any info
There is no easy to define limit to how "much" of a report can be rendered to PDF. It depends on size and complexity of the report and muscle of the server(s). Can you describe the hardware a bit? If you capture a perfmon while the hang is occuring, how does CPU, memory, disk, and network look? Also, is this reproducible? If you revert the change, does the report export?
Thank you.
|||
The machines running are 4 processor 3.2Ghz zeon's with 3GB Ram.
We didn't run perfmon, but normally get paged if there are performance issues.
I am able to reproduce this problem. I narrowed the recordset down to a particular agency, a subset of the full recordset The subset recordset contains 59 rows for the main report and 20 rows for the subreport, so it's not a large amount of data. We have agencies that are many times more complicated that print fine.
Strangely enough, I got it to work, when I pushed off the totals section to a subreport. That section had 2 running sums in it and some expressions based on data fields. I passed those over to the subreport as parameters and it worked fine.
This is not the only time, we've had this hang and had to look for a workaround. I also had a problem like this when I merged some columns in a table. The problem was happening to a completely different agency than before. Now when I develop in this report, I do one thing at a time, then deploy and run to be sure I didn't introduce another hang situation.
Is this behavior common to reporting services?
|||If the dataset is large, the report is complex, the hardware is underpowered, or some combination of the three, exporting to PDF can get bogged down, even in SQL Server 2005 SP2. One way to figure out what's going on is to look at the ExecutionLog table in the ReportServer database.
Execute the report and then look at the last 5 columns. If RowCount is large, consider pushing off some data filtering into the TSQL - this should also decrease TimeDataRetrieval. If TimeProcessing is large, see if there is anything you're doing in the report that can be better handled by SQL server - like, sorting or aggregation. Also, try and reduce the complexity of any expressions you have in the report. If TimeRendering is large, it could be you're running into inefficiencies in the rendering engine. We've made good progress and improved perf in RS 2005 but we're still not where I'd like us to be so have more work to do. If ByteCount is large, consider breaking the report up so users don't have to deal with 50M PDF files. If archival is your goal, you might consider a different format like HTML that is much easier to grep later than PDF. Tudor has some suggestions as well on http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx.
"Hanging" is not common, though slow rendering when there's a lot to do or not enough hardware to do it on is more common.
I don't understand why merging columns would make that much of an impact, however. If you have a repro, please open a bug so I can take a look. If you can't get to http://connect.microsoft.com, you can email me the details directly (click on my name to the left and you'll find my email address).
Thank you.
|||Hi Donovan,
Thanks for your help.
I've got a question still, I'm not sure that this is bug ticket worthy so I figured I'll run it by you here first.
This is from the ExecutionLog table.
This report was successful. This record is the full recordset and produced. I moved a totals section to a sub report to get it to work.
RequestType Format TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount
-- -- -- -- -- - --
0 PDF 2007-03-09 10:14:42.723 2007-03-09 10:22:30.500 19070 269112 179356 1 rsSuccess 8969543 8569
This recordset here, is one that didn't work. This code is prior to me moving the totals section out to a subreport. Also, this recordset is a subset, just one agency and it's associated records. After a half hour I cancelled the job.
The TimeRendering is very large and the TimeDataRetrieval also seems large for that recordset.
RequestType Format TimeStart TimeEnd TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount
-- - -- -- -- -- - - --
0 PDF 2007-03-08 10:47:09.303 2007-03-08 11:15:53.803 20642 2690 1700930 1 rsJobWasCanceled 0 131
The difference between the 2 versions of code, is that the one that's failing has 3 footer rows for the same grouping, 4 of the text boxes contain =FormatCurrency(Fields!XYZ.Value) type values and one contains an =IIF(Fields!CompType.Value = "SA", "XYZ Based Compensation", "ABC Based Compensation") + " Totals For This Period:"
I replaced those 3 footers, with one footer that calls a subreport, ironically, the sub reports parameters contains the same formatcurrency items and the iif.
I'm not sure what to think, it looks like a bug, but wouldn't TimeDataRetrieval indicate database side inefficiency?
What do you think?
Scott
|||That doesn't sound right - time rendering seems too high. Would you please email me a copy of the rdls so I can see what's happening in the code?
The TIFF and PDF renderer share a lot of the same code. Is TIFF also that slow in exporting that report?
Regarding the TimeDataRetrieval column, if you take the queries out of the reports and run them against the server manually, how do the times compare?
Thank you.
No comments:
Post a Comment