I have a report that needs to be generated in large quantities and sent to clients, so performance is of utmost necessity.
I've realized by monitoring SQL Server Profiler that all the available parameter values are queried twice, causing a few milliseconds of time wasted.
This however is not as serious as the fact that the main datasource queries are run twice as well. These cause quite a performance hit when generating reports.
After some investigation it seems that one of the tables referencing the datasource in the report is what is causing the repeat query. I find this strange since none of the subreports that use the same datasource cause the same behaviour.
The datasource value of the table is set to my maindatasource and no additional calculation is done on the data. The table literally just displays field values directly from the datasource
Why is my database getting hit more than once to retrieve exactly the same data?
The datasource is of type Telerik.Reporting.ObjectDataSource and I'm using 2015 Q3
5 Answers, 1 is accepted
Most common reason for this behavior is that DataSource property is set to the same database for both Table item and Table's parent item (Report). As it is a common requirement that the data needed for your Table or another data item will already be present in the parent's data source, in this case you can improve the report performance by reusing the data from the parent, instead of creating and configuring a new data source component to retrieve the data anew. You can reuse the data by adding a Binding to the data item's DataSource property, which sets the following expression: = ReportItem.DataObject.
For more information, please refer to the Binding a Data item to Data and How to use the ReportItem.DataObject property in expressions help articles.
If the above information does not help, please open a support ticket and attach a runnable demo project that reproduces this issue. This way we can check your settings and give you more accurate suggestions.
Thank you for the reply Katia.
This binding approach does work when there is a report data source set, but in my case I did not have a report data source.
After some restructuring on the various tables and subreports using this datasource, I was able to use one report data source and bind all the tables to that source.
Great article references.
It is nice to hear that the problem was resolved.
In general, the number of executed queries depends on how many data items with their own data sources appear in the report at its processing stage.
Some advice on how to optimize the performance of the report is also provided in Performance Considerations article.
I know this is an old post, but maybe you can shed some light on my test.
I'm using your suggestion:
- adding a Binding to the data item's DataSource property, which sets the following expression: = ReportItem.DataObject
However, the multiple tables I have in the report only acquire partial data (only the first row shows in the tables). As I change the tables back to the data source (no binding), all data is shown. Is this expected?
The binding to ReportItem.DataObject will bind to the data from the parent item's data scope. Depending on the nesting used in the report and the location of the table this may yield different results. Imagine the following three scenarios:
1. A report is bound to data from a SQL table with 100 rows. Then a table is nested in the report header section and bound to ReportItem.DataObject.
Result: the table will display the whole set of 100 rows.
2. A report is bound to 100 rows of data from a SQL table. A table is nested in the report's detail section and bound to ReportItem.DataObject.
Result: the table will display a single data row - the row of the current detail section instance.
3. A report is bound to the same data, but this time grouping is applied on the report level. A table is nested inside the group header section and bound to the data object of the parent data scope.
Result: the table will display only data rows which match the current group's grouping criteria.
For more information on the report sections check Report Structure. Data scopes are discussed in Expression Scope.