CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008-r2
Fastidious
Fastidious

Reputation: 1319

Does SSRS run multiple queries at once?

I'm trying to optimize a report that uses multiple stored procedures on the same table. Unfortunately, each procedure is reading millions of records and aggregating the results. It's a very intense read for a report, but each stored procedure is optimized to run pretty fast within SSMS.

I can run each stored procedure and get a result set within 10 to 20 seconds. When I put them all into one report within SSRS, the report times out.

There is a total of 4 parameters per stored procedure. All targeting the same table, just aggregating the data in different ways. Indexes on those tables are inline with the query. It's based on time, user and the one dimension I'm using to COUNT() both DISTINCT and NONDISTINCT.

I'm thinking the issue is the fact SSRS is running 4 procedures at the same time on the same table as opposed to one after the other. Is this true? If so, is there anyway to ensure SSRS does not run them in parallel?

My only option is to create summary table that is already preaggregated. Then just run the report off that table. Otherwise, I guess param sniffing is possible too.

Upvotes: 5

Views: 4656

Answers (1)

David Tansey
David Tansey

Reputation: 6023

By default, datasets in SSRS are executed in parallel.

If all of your datasets refer to the same datasource, then you can configure for serialized execution of the datasets on a single connection this way:

  • open the data source dialog in report designer
  • ensure that the Use Single Transaction checkbox is checked

Once that checkbox is selected, datasets that use the same data source are no longer executed in parallel.

I hope that solves your problem.

Upvotes: 8

Related Questions