SQL Server Reporting Services - Fast TimeDataRetrieval - Long TimeProcessing

Question

An application that I support has recently begun experiencing extended periods of time required to execute a report in SQL Server Reporting Services. The reports that are being executed are not terribly complex. There are multiple stored procedures (between 5 and 8) which return anywhere from a handful to 8000 records total. Reports are generally from 2 to 100 pages. One can argue (and I have) the benefit of a 100 page report, but the client is footing the bill.

At any rate, the problem is that even the reports with 500 records (11 pages) being returned takes 5 minutes to return to the browser. In the execution log the TimeDataRetrieval is 60 seconds, but the TimeProcessing is 235 seconds. It seems bizarre to me that my query runs so quickly, but it takes Reporting Services so long to process the data.

Any suggestions are greatly appreciated.

Kind Regards,

Bernie

Answer 1

maybe you have an expression that needs long time for processing. please check this.

Answer 2

Forgot to post an update to this. I found the problem. The problem was associated with an image with an external source on the report. Recently the report server was disallowed internet access. So when reporting services was processing the report, it was trying to do an HTTP GET, to retreive the image. Since the server was disallowed outbound internet access, the request would eventually timeout with a 301 error. Unfortunately this timeout period was very long, and I suspect it happened for each page of the report, becase the longer the report, the longer the processing time. At any rate, I was not able to get outbound internet access reopened on the server so I took a different path. Since the web server where the image was hosted and the reporting server were on the same local network, I was able to modify the HOST file on the reporting server with the image hosts domain and local IP address. For example:

www.someplacewheremyimageis.com/images/myimage.gif

reporting server would try to resolve this via its local dns and no doubt get external ip address X.X.X.X

so I modified the HOST file on the report server by adding the the following line

192.168.X.X www.someplacewheremyimageis.com

So now when reporting services tries to generate the report it resolves to the above internal IP address and includes the image in the report.

The reports are now running snappier than ever.

Its these kinds of problems that you figure out with a flash of inspiriation at 4:30 am after hours of beating your head against your keyboard, that make it wonderful and terrible to be a software developer.

Hope this helps someone.

Thanks,

Bernie