Sql-server – Optimizing the PDF Export of Huge Reports in Sql Reporting Services 2005


First off I understand that it is a horrible idea to run extremely large/long running reports. I am aware that Microsoft has a rule of thumb stating that a SSRS report should take no longer than 30 seconds to execute. However sometimes gargantuan reports are a preferred evil due to external forces such complying with state laws.

At my place of employment, we have an asp.net (2.0) app that we have migrated from Crystal Reports to SSRS. Due to the large user base and complex reporting UI requirements we have a set of screens that accepts user inputted parameters and creates schedules to be run over night. Since the application supports multiple reporting frameworks we do not use the scheduling/snapshot facilities of SSRS. All of the reports in the system are generated by a scheduled console app which takes user entered parameters and generates the reports with the corresponding reporting solutions the reports were created with. In the case of SSRS reports, the console app generates the SSRS reports and exports them as PDFs via the SSRS web service API.

So far SSRS has been much easier to deal with than Crystal with the exception of a certain 25,000 page report that we have recently converted from crystal reports to SSRS. The SSRS server is a 64bit 2003 server with 32 gigs of ram running SSRS 2005. All of our smaller reports work fantastically, but we are having trouble with our larger reports such as this one. Unfortunately, we can't seem to generate the aforemention report through the web service API. The following error occurs roughly 30-35 minutes into the generation/export:

Exception Message: The underlying connection was closed: An unexpected error occurred on a receive.

The web service call is something I'm sure you all have seen before:

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

The odd thing is that this report will run/render/export if the report is run directly on the reporting server using the report manager. The proc that produces the data for the report runs for about 5 minutes. The report renders in SSRS native format in the browser/viewer after about 12 minutes. Exporting to pdf through the browser/viewer in the report manager takes an additional 55 minutes. This works reliably and it produces a whopping 1.03gb pdf.

Here are some of the more obvious things I've tried to get the report working via the web service API:

  • set the HttpRuntime ExecutionTimeout
    value to 3 hours on the report
  • disabled http keep alives on the report server
  • increased the script timeout on the report server
  • set the report to never time out on the server
  • set the report timeout to several hours on the client call

From the tweaks I have tried, I am fairly comfortable saying that any timeout issues have been eliminated.

Based off of my research of the error message, I believe that the web service API does not send chunked responses by default. This means that it tries to send all 1.3gb over the wire in one response. At a certain point, IIS throws in the towel. Unfortunately the API abstracts away web service configuration so I can't seem to find a way to enable response chunking.

  1. Does anyone know of anyway to reduce/optimize the PDF export phase and or the size of the PDF without lowering the total page count?
  2. Is there a way to turn on response chunking for SSRS?
  3. Does anyone else have any other theories as to why this runs on the server but not through the API?

EDIT: After reading kcrumley's post I began to take a look at the average page size by taking file size / page count. Interestingly enough on smaller reports the math works out so that each page is roughly 5K. Interestingly, when the report gets larger this "average" increases. An 8000 page report for example is averaging over 40K/page. Very odd. I will also add that the number of records per page is set except for the last page in each grouping, so it's not a case where some pages have more records than another.

Best Solution

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

1) Unless images are JPG or PNG colour type 3, they are expanded to BMP's See here

2) Unless you configure SSRS to behave otherwise (not recommended), then SSRS will embed fonts or font subsets into the PDF, unless they are one of the 5 'standard' PDF fonts.

Although none of the standard fonts (other than Symbol I guess) are installed on most Windows OS's out of the box, we've found that if you use Times New Roman, Courier New, or Arial then forward and reverse font substitution will take place.

The easiest way to convert your RDL's is to view them as XML and search and replace the FontFamily tags.

If you have to use a non standard font, then, you can still minimize the damage:

  • Use as few fonts as you can. Search through the RDL XML to make sure there aren't any redundant fonts.
  • Use TTF fonts if you use different sizes of the font.
  • Try not to mix normal, bold and italic variants of the font, else it will be embedded multiple times.
Related Question