Large Report Implementation Reccomendations 250 - 300 pages

Question

My group has a report that was recently developed utilizing Reporting Services 2005 with the NeoDynamics Bar Code component. The report is for return authorizations and consists of 2 main reports.

The first set of data on the report is the authorization form, listing all of the line item products and some header information (think of it as an invoice).

The second item on the report is the listing of all of the products which prints out 4 products per page. Each product has 3 bar codes and 1 logo.

The average return authorization is produced in just a couple of seconds and streamed to the user as a PDF. The average document is about 6 - 8 pages in length. We also have some return authorizations (unfortunately) that can get up to about 300 pages (less than 10% of authorizations are more than 15 pages). Reporting Services seems to timeout after minutes of trying to put the report together as a PDF.

My question is, what is our best option for getting this report into a PDF format? Our users are allowed to generate the report 24x7 and it is streamed to them as a PDF. Are there things we can do to improve the reporting services performance?

Answer 1

I'm not particularly familiar with Reporting Services. I think it's a Microsoft product. Which means that you're probably talking to a SQL Server database on the back end. And so there are two pieces of advice.

1. check the client and server configuration because there is a query timeout value set somewhere and you are likely encountering that limit.
2. You are better off extracting all of the necessary data into temp tables or some sort of cache so that you can process the data in a more reasonable timeframe.

(2) is a good idea anyway... it will prevent lock escalation and related badness.