Report is repeatedly sending the same query to the server.

[Michael]

I have a report with two data sources. There is not anything complicated about my report, but I do have 18 Report Parameters. The report takes a good 5 minutes to generate. When I performed a SQL trace, the first query is sent to the server repeatedly, (this is the slow-down) whereas the the other query is sent just once. Why is it doing this? Report is attached.

Thanks!

[Dimitar]

If I understand correctly, by the two queries, you mean the two SqlDataSource(Actual & Budget) and their SelectCommand queries, is that correct?

The Actual data source is used a couple of times in the report and that is why the query is executed multiple times. There are 4 tables inside the report, and 3 of them are set to the Actual data source, plus the Report itself also being set to that data source component. The group section itself seems to be a static group section because there is no grouping field for that group.

With that being said, I believe that you have two approaches here which could improve the performance:

• Considering that the tables all have their own data object because they are all set to one of the two data sources, and also considering that the group section is static, you do not need to have the data source of the report set to anything. You can set the Report.DataSource back to none, and that should be one query less at least and a small improvement on the performance.
• The second approach is to actually keep the Report.DataSource and instead set a binding on the DataSource property of each table that uses the Actual data source. The binding expression could be the ReportItem.DataObject, this way the tables will not use their own data scope but will instead get the data from the report and the query should only run once in this approach. Note, however, that you need to apply the filters of the tables to the Report.Filters since the tables will now be using the report's data. For more information, see the How to use the ReportItem.DataObject property in expressions article.