Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Controlling dataset processing time in SQL Server Reporting Services reports


By:   |   Last Updated: 2011-03-18   |   Comments (1)   |   Related Tips: > Reporting Services Performance

Problem

Long running queries in complex reports is one key performance optimization area. One way to keep your users from waiting for a long period of time to view a report, due to corrupt queries or poorly designed queries, is to configure your reports with an acceptable time limit to wait for the database engine to return data. In this tip, we will look at how to implement this for SSRS reports.

Solution

We usually want our reports to wait for data for a reasonable period of time, but not indefinitely. There are several ways to create a time limit for your report execution and one of the best ways is to implement a "timeout" limitation on entities that request data from the database engine, i.e. datasets.

Reports generally have two sections where a timeout can be applied - Data Source Connection Timeout and Dataset Timeout. Often developers confuse where to apply the timeout and set it at the connection level. It is valid to set a timeout property on the connection, but in the case where a connection is established in time the query may keep fetching data for a long period of time. This is where we need to apply a time restriction on the dataset.

For an embedded dataset the default timeout is 30 seconds and for a shared dataset the default is no timeout limit. You can read more about this here.

To simulate the problem, follow the below steps:

Step 1

Create a simple SSRS report containing some data. I created a report that looks something like the below screenshot. I am using an embedded dataset in this report, which calls a stored procedure to retrieve data from the database.

implementing a time limit to wait for the database engine to return data in ssrs reports

STEP 2

Now to simulate the problem of a long running query. Let's make the stored procedure take more time to complete by adding a WAITFOR DELAY in the stored procedure. Modify the SP as shown in the screenshot below to introduce a delay of 35 seconds.

create a simple ssrs report

STEP 3

If you go back to the report and check the "timeout" property of the dataset, as shown below, you can see that it is set 30 seconds which is the default timeout period for an embedded dataset.

in dataset properties, check the timeout property of the dataset

STEP 4

Execute your report using Reports Manager and you will see that after 30 seconds the report will fail with an error as shown in the below screenshot. Although this error formatting is not the best option to show to users, it's better than having them wait indefinitely.

As you can probably tell, the dataset processing was terminated since the data retrieval took longer than the timeout value that was set for the dataset.

as result, the report will because the dataset processing took longer than the timeout value


Summary

As you can see you have the ability to set the timeout limits for a query. This will allow you to either increase the value for an embedded dataset or set a value for a shared dataset. This way you can better control the user experience instead of queries not finishing in time or taking way too long.

Ideally you should measure the average amount of time a query takes to retrieve results and tune your query response and timeout property to work in optimal harmony. For complex and long running queries you will need to set this value appropriately, so the report does not run forever, but also has enough time to complete the query.

Next Steps
  • Open an SSRS report file in Notepad and check whether you are able to find the dataset timeout setting in the report elements
  • Check out whether you can set the timeout property on a shared dataset
  • Learn more about setting timeout values in SSRS reports
  • Read these other SSRS tips


Last Updated: 2011-03-18


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, June 19, 2013 - 8:57:25 AM - joy Back To Top

hi,

I am displaying the report in my web application.But when i click the button to fetch report from report server and disaply, i m getting time out issue.

I  have increaed the timeout for Dataset 3600 sec. Then also i m getting.

I am fetching like 3000 records. Cud u plz help me out how to dispaly this report?

 

 

Thanks & Regards,

Jo


Learn more about SQL Server tools