CODEWITHSUNDEEP
performancereporting-servicesssrs-2008
llasarov
llasarov

Reputation: 2111

How to improve DB load produced by SSRS reports

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!

Upvotes: 0

Views: 101

Answers (2)

SKMNeenah
SKMNeenah

Reputation: 1

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.

Upvotes: 0

Mike Honey
Mike Honey

Reputation: 15037

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.

Upvotes: 1

Related Questions