Optimizing export of huge PDF reports to Sql Reporting Services 2005

Question

First, I understand that it is a terrible idea to run very large / lengthy reports. I know that Microsoft has a rule confirming that SSRS report requires no more than 30 seconds. However, sometimes gigantic reports are the preferred evil due to external forces consistent with state laws.

In my place of work, we have an asp.net (2.0) application that we have migrated from Crystal Reports to SSRS. Due to the large user base and the complex requirements for the reporting user interface, we have a set of screens that accept user-entered parameters and create schedules that will be executed during the night. Since the application supports several reporting frameworks, we do not use SSRS planning / snapshot tools. All reports in the system are generated by the planned console application, which accepts the parameters entered by the user and generates reports with the corresponding solutions for the reports with which the reports were created. In the case of SSRS reports, the console application generates SSRS reports and exports them as PDF files through the SSRS web services API.

So far, SSRS has been much easier to handle with anything besides Crystal, with the exception of the 25,000-page report we recently converted from Crystal reports to SSRS. SSRS Server is a 64-bit 2003 server with 32 gigabytes of RAM running SSRS 2005. All of our smaller reports work fantastically, but we are having problems with our larger reports, such as this one. Unfortunately, we cannot generate the above report through the web services API. The following error occurs after about 30-35 minutes during the generation / export process:

Exception message: The underlying connection was closed: An unexpected error occurred while receiving.

A web service call is what I'm sure you saw everything before:

data = rs.Render(this.ReportPath, this.ExportFormat, null, deviceInfo, selectedParameters, null, null, out encoding, out mimeType, out usedParameters, out warnings, out streamIds); 

It is odd that this report will be launched / displayed / exported if the report is run directly on the report server using the report manager. The process that creates the data for the report runs for about 5 minutes. The report is displayed in native SSRS format in the browser / view after about 12 minutes. Exporting to pdf via a browser / viewer in Report Manager takes an additional 55 minutes. This works reliably and creates an amazing pdf file of 1.03gb size.

Here are some of the most obvious things I tried to get the report to work through the web services API:

• set the HttpRuntime ExecutionTimeout value to 3 hours in the server report
• disabled http keep alives on report server
• increased script latency on the report server
• set the report to never log on to the server
• set the report timeout for several hours when calling a client

From the settings I tried, I'm pretty comfortable saying that any problems with the timeout have been fixed.

Based on my research on the error message, I believe the web service API does not send responses by default. This means that he is trying to send all 1.3gb over the wire in one answer. At some point, IIS throws a towel. Unfortunately, the API abstracts the web services configuration, so I cannot find a way to enable the response response.

• Does anyone know in any case to reduce / optimize the PDF export phase and or PDF size without reducing the total number of pages?
• Is there a way to enable lock response for SSRS?
• Does anyone have any other theories about why this works on the server, but not through the API?

EDIT: After reading the kcrumley message, I started looking at the average page size, taking the file size / number of pages. Interestingly, in smaller reports, math works so that each page is approximately 5K. Interestingly, when the report gets larger, this “average” increases. For example, an 8000-page report is an average of more than 40 K / pages. Very strange. I will also add that the number of entries per page is set with the exception of the last page in each group, so this is not the case when some pages have more entries than others.

Answer 1

• Does anyone know one way or another to shorten / optimize the PDF export phase and or PDF size without reducing the total number of pages?

I have some ideas and questions:
1. Is this graphic material? If not, do you have tables that start as text but are converted to graphics using the SSRS PDF renderer (check if you can select the text in the PDF file)? 41K per page may be more than it should be, or it may not be, depending on how dense your report is. But we had cases when we had minor problems with the layout of the reports, for example, that the table had blood in the page margins, which led to the SSRS PDF rendering throwing up hands and displaying the table as an image, not as text , Obviously, the less graphics in your report, the smaller the size of your file.
2. Is there a way that you could easily break a report into pieces? For example, if this is a 10-local report in which location 1 corresponds to location 2, etc., in your final report, could you run the site of the 1st site regardless of the site of the 2nd place, etc.? If so, you can join the 10 sub-reports in one final PDF file using PDFSharp after you receive them. This leads to some difficulties with pagination, but nothing can be insurmountable.

3. Does anyone else have theories about why this happens on the server, but not through the API?

My guess would be the only report size. I don’t remember everything about configuring IIS and what SSRS is, but there may be some general IIS settings (possibly in Metabase.xml) that you will need to update to even allow the passage of large amounts of data.

You can highlight the question of whether time is a problem by taking one of your working reports and building long-term latencies in your stored procedures using WAITFOR (assuming SQL Server for your DBMS).

Not decisions as such, but ideas. Hope this helps.

Answer 2

We narrowed down large PDF exports from SSRS and found 2 main culprits

1) If the images are not JPG or PNG type 3 colors, they expand to BMP. Look here

2) If you have not configured SSRS to behave differently (not recommended), then SSRS will embed fonts or subsets of fonts in PDFs if they are not one of the 5 'standard' PDF fonts .

Although not one of the standard fonts (except Symbol, I think) was installed on most Windows OS initially, we found that if you use Times New Roman, Courier New, or Arial , then the font will be replaced directly and indirectly.

The easiest way to convert RDLs is to look at them as XML and look for and replace FontFamily tags.

If you need to use a custom font, you can still minimize the damage:

• Use as few fonts as possible. Search through RDL XML to make sure there are no extra fonts.
• Use TTF fonts if you use different fonts.
• Try not to mix regular, bold, and italic font options, otherwise it will be embedded several times.

Answer 3

Obviously, this is a huge report, in fact it is closer to the 1.3 GB database than the report.

Have you thought to find a way to break it into several parts, and then combine them? (Use one of several ways to merge the PDF files listed on this site.)