Master-Detail Report Performance

[Carla Lewis]

I've followed along with the video at http://tv.telerik.com/reporting/video/telerik_trainer_-_reporting_subreports.  This is basically the way I have created all of my Master-Detail reports.  I use a ReportParameter/Filter combo on the subreport and a corresponding parameter on the master report.

However, on one report in particular it is very, very slow.  Sixty seconds.  That's painfully slow to me.  The master report lists on the most basic fields from a Driver's record:

SELECT id, firstName, middleName, lastName, phone1, phone2, phone3, city, experience, endorsements, class, quality, hired, applied
From  vw_dmsDriver
WHERE (Assigned=0) AND (AvailabilityID = 1) AND MarketId=1

The subreport contains all the individual notes for that driver from a related table:

SELECT [Value]
      ,Username
      ,DriverID
      ,[TimeStamp]
  FROM vw_dmsDriverNotesPrivate

I don't know the ins-and-outs of what's going on behind the scenes, but I really don't think it should take that long.

So I was then reading this: http://www.telerik.com/help/reporting/designing-reports-master-detail.html, and it pointed me to this video  http://tv.telerik.com/reporting/video/Telerik_Reporting_-_Design_Time_Support_for_Parameterized_Queries.  I thought this might enable me to get a little better performance out of the report by passing parameters to the DataAdapter's SelectCommand, rather than using the ReportParameter/Filter.

Unfortunately, the example code at the bottom of the http://www.telerik.com/help/reporting/designing-reports-master-detail.html doesn't seem to work.

Running a report for either of these queries separately, is near instantaneous.  It almost seems to me that the subreport is loading every record and ignoring the parameter, and then applying the filter after ALL records have been returned, but what do I know?

Any advice would be appreciated.  If there is any other detail I can provide, please let me know.

[Steve]

Hi Carla,

As you have noticed correctly, every time the report is rendered/previewed (paged and exported as well)  it is processed and rendered from scratch. Obviously, this is not the best way to do this but we currently do not have any caching mechanism. Every report instantiation will cache the whole report in the server's memory and this is per session. This cache is not only data, but the whole report being processed - and every report item has numerous properties.
So your assumption that the whole dataset is being loaded and then filtered is correct - currently there is no other way to proceed when working with report parameters/filters.
Your approach of using the NeedDataSource as per the video is on the right track. We apologize for the code snippets at the end - indeed they are outdated as we've introduced new data engine in the Q1 release and forgotten to update this article. Your Telerik points have been updated for bringing this to our attention.
Here is a KB article that explains the changes that need to be applied: ReportItemBase.DataItem property (Telerik.Reporting.Processing) has been replaced by property DataObject
or that code specifically would be changed to:

 private void subReport1_NeedDataSource(object  sender, System.EventArgs e)
       {
         Processing.ReportItemBase item = (Processing.ReportItemBase)sender;
         this.subReport1.Parameter1 = item.DataObject["PurchaseOrderID"] as string
       }

Please excuse us for the inconvenience.

Greetings,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.