By: Siddharth Mehta | Comments (1) | Related: > 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.
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.
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.
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.
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
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
