SSRS Parameters - Values from a query (parallelism)

5
votes

I have SSRS report that has a single data source - SSAS Tabular cube.

The report has 15 parameters that gets their values from the queries (datasets).

When a user opens the report, each parameter is populated, but each query execution is serialized (confirmed by Profiler / Execution Log). Each execution takes up to 70ms. As a result, it takes 1,000-1,200 ms just to open a report.

Is there a way to populate the report parameters in parallel?

Note that

  • when the report is running (user clicks "View Report"), all charts datasets are being executed in parallel, so SSAS/SSRS is definitely able to execute the queries in parallel.
  • "Use single transaction when processing the queries" checkbox is not checked for the data source.

SSRS/SSAS versions: 2016, latest SP/CU, Ent & Dev

UPDATE: if I change the data source to SQL Server, the issue persists, SSRS is not executing the queries (for Report Parameters) in parallel.

1
sql-serverreporting-servicesparametersparallel-processing
Are the parameters cascading or independent of each other? - Harry
@Harry, parameters are independent - Anton
If they're independent, why not have a single dataset to return all the values of the parameters, instead of 15 (one for each). - Larnu
@Larnu, many parameters are multi-valued, from different objects. And even when a single dataset is used for those 15 parameters, that dataset will be executed 15 times, one by one, not in paralel - check in Profiler - Anton
I'm not sure what your point is here, but it's wrong. A single statement returning multiple values won't be run multiple times; it'll be run once. If you have a dataset returning many values, which are assigned to different parameters, SSRS will run the dataset once; not once per parameter. - Larnu

1 Answers

1
votes

Could you double check whether the datasets were executed in parallel or not? By default, datasets in a report are executed in parallel no matter they are generated from a single data source or multiple data sources. In your scenario, since the datasets use the same data source, and the “Use single transaction when processing the queries” option is not checked for the data source, the datasets should execute in parallel.