How to improve DB load produced by SSRS reports

0
votes

I would like to know whether there is a posibility to reduce the DB load produced by SSRS reports. I have a SSRS report consisting of several sub-reports. Every one of them has at least one DB query.

  1. Many of them query the same data since the most sub-reports have a kind of template header filled with dynamic data.
  2. Some sub-reports are shown depending on whether a query returns any data. So once the data is queried to determine whether to show the report at all. Then the report itself queries the same data to show it in a table

In general I can tell that I need a mechanism to pass queried DB data from parent report to a sub-report. The parent report will query some data, it will iterate over the data sets and for every data set it will show a sub-report passing the current data set as a parameter.

I could not find a mechanism to pass the data set (data row). That's why I show the sub-report by passing a kind of data set ID. The sub-report itself queries the same data again, filters by the passed data set ID and shows only the relevant data set. This causes huge load on the DB.

Thank you in advance!

2
performancereporting-servicesssrs-2008
You need to analyse specific queries and optimise accordingly. If you have a total of 7 queries running, analyse all of them, find which is causing the 'load on the DB' and fix it. If you have concerns about master/detail type queries, you'll need to convert your report to run off just one query but this may not be possible (or any faster). Note that if you run the same query repeatedly (even with different parameters) you gain some efficiencies with caching etc. anyway. - Nick.McDermaid

2 Answers

1
votes

The design you describe is fairly standard and I would not expect it to cause "huge load on the DB". I would expect the DB load of running 10 filtered sub-reports to only be about 10-20% more than running one report covering the same 10 items.

I would add an index on the "data set ID" column to make that filter more efficient.

0
votes

Depending on complexity of your subreports using lookup function may be an acceptable faster solution. And previous comment about hiding rows or subreports with no data applies here too.