All geek questions in one place

Does SSRS run multiple requests at once?

I am trying to optimize a report that uses several stored procedures in one table. Unfortunately, each procedure reads millions of records and aggregates the results. This is a very intensive read for the report, but each stored procedure is optimized for quick execution in SSMS.

I can start each stored procedure and get the result in 10-20 seconds. When I put them in a single report in SSRS, the report expires.

The stored procedure contains only 4 parameters. All targeting the same table, just aggregating data in different ways. The indices of these tables are embedded in the query. It is based on the time, user, and one dimension that I use for COUNT () of both DISTINCT and NONDISTINCT.

I think the problem is that SSRS simultaneously starts 4 procedures in one table, and not one after another. It's true? If so, does it matter that SSRS does not start them in parallel?

My only option is to create a pivot table that has already been pre-grouped. Then just run the report from this table. Otherwise, I guess flirting is possible too.

+5
source share
1 answer

By default, the data set in SSRS runs in parallel.

If all your data sets belong to the same data source, then you can configure the sequential execution of data sets on the same connection in this way:

  • open the data source dialog in report designer
  • make sure the Use single transaction checkbox is selected .

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

I hope that solves your problem.

+6
source

Source: https://habr.com/ru/post/1238568/

More articles:


All Articles