Multiple Database hits for same datasource

6 posts, 1 answers
  1. Hendrik
    Hendrik avatar
    2 posts
    Member since:
    Jan 2016

    Posted 20 Jan 2016 Link to this post

    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  

  2. Answer
    Katia
    Admin
    Katia avatar
    693 posts

    Posted 21 Jan 2016 Link to this post

    Hello Hendrik,

    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.

    Regards,
    Katia
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. Hendrik
    Hendrik avatar
    2 posts
    Member since:
    Jan 2016

    Posted 22 Jan 2016 in reply to Katia Link to this post

    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.

  4. Katia
    Admin
    Katia avatar
    693 posts

    Posted 22 Jan 2016 Link to this post

    Hi Hendrik,

    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.


    Regards,
    Katia
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  5. Mosart
    Mosart avatar
    10 posts
    Member since:
    May 2012

    Posted 04 Jan in reply to Katia Link to this post

    Hello Katia,

    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?

    Thank you!

  6. Nasko
    Admin
    Nasko avatar
    1049 posts

    Posted 09 Jan Link to this post

    Hello Mosart,

    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.

    Regards,
    Nasko
    Progress Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top