We have several reports on SSRS 2008 which we have accessing through a c# web page using reportviewer control. One report, takes around 10 minutes to run the query is getting timed out aftrer executing the query. I have tried out the time out settings of report data source, site settings of the report manager and the particular report, the time out of IIS in which the application is hosted and the connection time out of shared data source.
Even after that report failed. The query is working because it is generated using stored procedure and creates a named temporary table(tmp_123) for reporting. After running the query I am able to view the rows in the table.
Please advise what to do in detail.
asked Apr 08 '10 at 11:02 AM in Default
IDK much about SSRS but I suggest you start by running the query in question from studio management and if it is slow like you said, tune your query accordingly. Also fire profiler and see whats happening behind the scenes.
answered Apr 08 '10 at 11:18 AM
My suggestion would be to tune the query. Get the execution plan and identify where and why it is performing slowly and change it so that it runs faster. 10 minutes is a very long time to wait, even for a report.
Another option, assuming you're doing some sort of data aggregation is to pre-aggregate the data. What I mean is that you can build a real table and load the data into it on a regular basis, before the report is called.
answered Apr 08 '10 at 11:24 AM
Grant Fritchey ♦♦
There is a property of the ReportViewer control that defaults to 10 minutes (The default value is 600000 milliseconds.) You can change it in your aspx page or code behind like this... ReportViewer1.ServerReport.Timeout = 120 * 60 * 1000; // Two hours
as long as your users don't mind waiting, this should solve the timeout problem.
answered Jul 11 '12 at 11:03 PM
As @mwilson mentioned the property of the ServerRport which can be set to -1 to have no timeout, I suggest going by finetuning the queries or try to build pre-aggregations as @Grant has mentined because there are also other issues related to the long running reports.
As you have mentioned, even the report rendering failed when you try to show it in the ReportViewr Controlm (due to timeout), the real issue is that the SSRS service continues in the report generation even after the failure, Especially in multiuser environment you can easily overload the server.
The same issue happens when you cancel the report rendering directly in the SSRS Reports manager. Even you have cancelled a report, which has a long running query under the hood, SSRS Service coninues in execution of the long running query until it finishes. When you run the report and cancel it multiple times, you will have multiple long running queries being executed on the SQL Server side.
So be carefull with the long running queries as when users will have to wait a long time for a report, they will start to cancel and re-launch the reports several time so see if it will quicker in next run.In case there is no much possibilities of speeding up the queries behind the hood, an option could be to generate a snapshot of the port or cache the data periodically (daily or more often if necessary). Then when the report will be launched it will use the stored snapshot or cached data and the report will run nearly instantly. This will save you a lot of SQL Server resources especially in multi-user environment as mentioned above.
answered Jul 12 '12 at 05:34 AM