SQLDataSource being populated multiple times (for each master record)

[Patrick Saunders]

Hi guys,
I've got a report, with the Table items inside the section. I use this to show child data.
I use filter expressions on the Tables to only show the relevant child data.

I've having performance issues, and when I trace the SQL I can see that the SqlDataSource is being repopulated for every master record.
Surely this is not correct? But I'm not sure the correct way to configure this?

See screenshot showing how one of my Table objects is configured.

Thanks,

Pat.

[Stef]

Hi Pat,

The SqlDataSource will retrieve data for each rendered Table item. You can use ObjectDataSource or other data source component with data model created base on the existing database. Then the nested tables can be bound to the corresponding collection in the model or to the data related to the container item. For more details take a look at the following articles:

• How to use ReportItem.DataObject property in expressions
• How to Databind to Collection Properties

If the data is preloaded, this will save you time from connecting to the database. Other approach is to use stored procedures which will be executed faster than their plain Select -statements equivalents.

I hope this helps you.

Regards,
Stef
Telerik

[Patrick Saunders]

Hi Stef,
I realised that I must have misunderstood how the Table items are rendered.

So if I undertand you correctly, you can only get full control of loading the data by using ObjectDataSource?

For most of my reports where I have multiple parent records, I had assumed that I could load the child datasource ONCE(ie sdsOption1,sdsOption2 in my case) and then use the Table item filter expression to only show data related to the current parent record.
Just to clarify, is there any way to cancel the reload of data for the SqlDataSource in events or anything?

Thank you,

Pat.

[Stef]

Hi Pat,

For each new rendered Table item bound to SqlDataSource there will be executed a new connection to the database. Thus if you need to avoid this, it is better to preload the whole data in a in-memory object as a DataTable(DataSet) and use it from there via ObjectDataSource. On report level, you can apply filters to get only the needed set of data for a single Table item.

The other approach is to build a model. Load the the data before processing the report and assign that data to the report. The benefit is you can rely on the relations in the data and avoid creating new data source objects for the inner data items, as you can use expressions based on the collection properties and the currently associated data to an item(ReportItem.DataObject).

Regards,
Stef
Telerik