Dataset Execution Order

  • Article

A Reporting Services report can contain multiple dataset definitions.  By default, datasets are executed in parallel.  Now, in some situations you may not want this parallel query execution behavior - for example:

  • Your data source system cannot handle too many parallel connections.
  • You are executing queries with side-effects (e.g. update statements) and need a particular sequence of dataset executions (e.g. the first datasets returns one set of data and prepares temporary tables, that are retrieved by a second dataset query)

There are several ways of serializing dataset executions.

Serializing dataset executions for a particular data source:  

Since the initial release of Reporting Services, there is a RDL setting for serializing dataset executions on the same data source connection (and therefore only using one data source connection for multiple datasets). 

To apply this setting, open the data source dialog in report designer, and select the "Use Single Transaction" checkbox.  Once selected, datasets that use the same data source are no longer executed in parallel.  They are also executed as a transaction, i.e. if any of the queries fails to execute, the entire transaction is rolled back. 

The order of the dataset execution sequence is determined by the top-down order of the dataset appearance in the RDL file, which also corresponds to the order shown in report designer.

Serializing dataset executions when using multiple data source:  

Note that datasets using different data sources will still be executed in parallel; only datasets of the same data source are serialized when using the single transaction setting.  If you need to chain dataset executions across different data sources, there are still other options to consider.

For example, if the source databases of your data sources all reside on the same SQL Server instance, you could use just one data source to connect (with single transaction turned on) and then use the three-part name (catalog . schema . object_name) to execute queries or invoke stored procedures in different databases. 

Another option to consider is the linked server feature of SQL Server, and then use the four-part name (linked_server_name.catalog . schema . object_name) to execute queries.  However, make sure to carefully read the documentation on linked servers to understand its performance and connection credential implications.