x

SSRS 2008 Time out for lengthy queries

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.

more ▼

asked Apr 08 '10 at 11:02 AM in Default

BIJU THOMAS gravatar image

BIJU THOMAS
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort newest

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.
more ▼

answered Jul 12 '12 at 05:34 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jul 11 '12 at 11:03 PM

mwilson gravatar image

mwilson
20

+1 Exactly ServerReport.Timeout Property. But anyway as others I suggest to fine-tune the queries in stored proc to get better performance. Also it is important to take in mind the rendering time i fthe number of records returned is very higgh, This can be analysed from the [ReportServer].[dbo].[ExecutionLogStorage] tabke,

Jul 12 '12 at 05:14 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 08 '10 at 11:24 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.7k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 08 '10 at 11:18 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

+1 Great idea! I was slow.
Apr 08 '10 at 11:25 AM Grant Fritchey ♦♦
Thanks! I am learning every day here from the masters :-)
Apr 08 '10 at 01:07 PM DaniSQL
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x535
x18

asked: Apr 08 '10 at 11:02 AM

Seen: 3735 times

Last Updated: Jul 12 '12 at 05:34 AM