优化Sql Reporting Services 2005中的巨大报表的PDF导出

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.

首先,我了解运行极大/长时间运行的报告是一个可怕的想法。我知道微软有一条经验法则表明SSRS报告的执行时间不应超过30秒。然而,由于符合国家法律的外部力量,有时候庞大的报告是首选的邪恶。

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.

在我的工作地点,我们有一个asp.net(2.0)应用程序,我们已经从Crystal Reports迁移到SSRS。由于庞大的用户群和复杂的报告UI要求,我们有一组屏幕接受用户输入的参数并创建要在夜间运行的计划。由于应用程序支持多个报告框架,因此我们不使用SSRS的调度/快照工具。系统中的所有报告都是由计划的控制台应用程序生成的,该应用程序获取用户输入的参数,并使用创建报告的相应报告解决方案生成报告。对于SSRS报告,控制台应用程序生成SSRS报告并通过SSRS Web服务API将其导出为PDF。

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:

到目前为止,SSRS比Crystal更容易处理,除了我们最近从水晶报告转换为SSRS的25,000页报告。 SSRS服务器是一台64位2003服务器,运行SSRS 2005时有32台ram。我们所有较小的报告都运行得非常好,但是我们的报告比较困难。不幸的是,我们似乎无法通过Web服务API生成上述报告。生成/导出大约30-35分钟发生以下错误:

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.

奇怪的是,如果使用报表管理器直接在报表服务器上运行报表,则此报表将运行/ render / export。为报告生成数据的proc运行大约5分钟。报告在大约12分钟后以浏览器/查看器中的SSRS原生格式呈现。通过报表管理器中的浏览器/查看器导出为pdf需要额外55分钟。这可靠地工作,它产生了惊人的1.03gb pdf。

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

以下是我试图通过Web服务API使报告工作的一些更明显的事情:

set the HttpRuntime ExecutionTimeout value to 3 hours on the report server

在报表服务器上将HttpRuntime ExecutionTimeout值设置为3小时

disabled http keep alives on the report server

已禁用http在报表服务器上保持活动状态

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.

根据我对错误消息的研究,我认为默认情况下Web服务API不会发送分块响应。这意味着它尝试在一个响应中通过线路发送所有1.3gb。在某个时刻,IIS引起了人们的注意。不幸的是,API抽象出Web服务配置,所以我似乎无法找到启用响应分块的方法。

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?

有没有人知道在不降低总页数的情况下减少/优化PDF导出阶段和/或PDF的大小?

Is there a way to turn on response chunking for SSRS?

有没有办法打开SSRS的响应分块?

Does anyone else have any other theories as to why this runs on the server but not through the API?

有没有其他人有任何其他理论为什么它在服务器上运行而不是通过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.

编辑:在阅读kcrumley的帖子后,我开始通过获取文件大小/页数来查看平均页面大小。有趣的是,对于较小的报告,数学运算得出结论,每页大约为5K。有趣的是,当报告变大时,这个“平均值”会增加。例如,8000页报告的平均值超过40K /页。很奇怪。我还要补充说,除了每个分组中的最后一页之外,每页的记录数都被设置,因此不是某些页面的记录多于另一个页面的情况。

3 个解决方案

#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?

有没有人知道在不降低总页数的情况下减少/优化PDF导出阶段和/或PDF的大小?

I have a few ideas and questions:
1. Is this a graphics-heavy report? If not, do you have tables that start out as text but are converted into a graphic by the SSRS PDF renderer (check if you can select the text in the PDF)? 41K per page might be more than it should be, or it might not, depending on how information-dense your report is. But we've had cases where we had minor issues with a report's layout, like having a table bleed into the page's margins, that resulted in the SSRS PDF renderer "throwing up its hands" and rendering the table as an image instead of as text. Obviously, the fewer graphics in your report, the smaller your file size will be.
2. Is there a way that you could easily break the report into pieces? E.g., if it's a 10-location report, where Location 1 is followed by Location 2, etc., on your final report, could you run the Location 1 portion independent of the Location 2 portion, etc.? If so, you could join the 10 sub-reports into one final PDF using PDFSharp after you've received them all. This leads to some difficulties with page numbering, but nothing insurmountable.

我有一些想法和问题:1。这是一个图形密集的报告吗?如果没有,您是否有以文本开头但由SSRS PDF渲染器转换为图形的表格(检查您是否可以选择PDF中的文本)?每页41K可能比应该更多,或者可能不是,这取决于您的报告信息密集程度。但是我们遇到过一些情况,我们在报告的布局上遇到了一些小问题,例如表格渗透到页面的边距,这导致SSRS PDF渲染器“抛出手”并将表格渲染为图像而不是文本。显然,报表中的图形越少,文件大小就越小。 2.有没有办法可以轻松地将报告分成几部分?例如,如果它是一个10位置的报告,其中位置1后面是位置2,等等,在您的最终报告中,您是否可以独立于位置2部分运行位置1部分,等等?如果是这样,您可以在收到所有PDF报告之后使用PDFSharp将10个子报告加入到一个最终PDF中。这导致页面编号出现一些困难,但没有什么是不可克服的。

3. Does anyone else have any other theories as to why this runs on the server but not through the API?

3.有没有其他人有任何其他理论为什么它在服务器上运行而不是通过API运行?

My guess would be the sheer size of the report. I don't remember everything about what's an IIS setting and what's SSRS-specific, but there might be some overall IIS settings (maybe in Metabase.xml) that you would have to be updated to even allow that much data to pass through.

我的猜测是报告的庞大规模。我不记得什么是IIS设置和什么是SSRS特定的,但可能有一些整体的IIS设置(可能在Metabase.xml中),你必须更新,甚至允许传递大量数据。

You could isolate the question of whether the time is the problem by taking one of your working reports and building in a long wait time in your stored procedures with WAITFOR (assuming SQL Server for your DBMS).

您可以通过使用其中一个工作报告并使用WAITFOR在存储过程中构建较长的等待时间(假设您的DBMS使用SQL Server)来隔离时间是否存在问题。

Not solutions, per se, but ideas. Hope it helps.

本身不是解决方案,而是想法。希望能帮助到你。

#2

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

我们缩小了SSRS的大量PDF出口,发现了两个主要元凶

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

1)除非图像是JPG或PNG颜色类型3,否则它们将扩展为BMP的参见此处

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.

2)除非您将SSRS配置为其他方式(不推荐),否则SSRS会将字体或字体子集嵌入到PDF中,除非它们是5种“标准”PDF字体之一。

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.

虽然大多数Windows操作系统都没有安装任何标准字体(除了我猜的符号),但我们发现如果你使用Times New Roman,Courier New或Arial,那么将进行正向和反向字体替换。。

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

转换RDL的最简单方法是将它们视为XML并搜索并替换FontFamily标记。

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.

使用尽可能少的字体。搜索RDL XML以确保没有任何冗余字体。

Use TTF fonts if you use different sizes of the font.

如果使用不同大小的字体,请使用TTF字体。

Try not to mix normal, bold and italic variants of the font, else it will be embedded multiple times.

尽量不要混合字体的普通,粗体和斜体变体,否则它将被嵌入多次。

#3

Obviously, its a huge report, in fact it's closer to a 1.3 GB database, than a report.

显然,它是一份巨大的报告,实际上它比报告更接近1.3 GB的数据库。

Have you thought of finding a way to split it into multiple pieces and then combine them together? (use one of several different ways to combine PDFs listed on this site.)

你有没有想过找到一种方法将它分成多个部分,然后将它们组合在一起? (使用几种不同方法之一来组合本网站上列出的PDF。)

#1