Slow Subreport Architecture (v15.04)

edited June 2014 in Subreports
We have a report where decided to take the main TdaQueryDataView
(TdanxQueryDataView actually) and make it a "detail view" so that in
effect it now supplies the data for a child subreport. The link between
the two query views is parameterized, The main report's new
TdaQueryDataView will ALWAYS retrieve just one record, so the new
subreport should only fires ONCE.

In the original report if 400 records are retrieved and about 30 pages
need to be produced, the whole thing runs in a few seconds, if that.

In the new report it takes an age..so long that I don't think I've ever
got to the end of previewing it I get so bored waiting. What I get to
see, very slowly, is a feedback label saying "Calculating Page X". All
the grouping and sorting of data in the old main report and the new
subreport design are essentially the same, and if I turn off the
subreport (there are others) by setting it is visibility to false then
the new report again runs in a second or so. Admittedly the new
subreport itself contains subreports but if I turn those off it makes no
difference.

Is the something awry with the efficiency of using subreports? I could
understand if the detail TdaQueryDataView was firing each time one of
many records in the main report was traversed, but this is not the case.
The data retrieval burden is essentially the same in the old and the new
design (OK, maybe a few amount of extra overhead is introduced by two
queries firing, but nothing significant). Any thoughts?

Paul

Comments

  • edited June 2014
    Hi Paul,

    As a test I created a simple app that has a single master and multiple
    details connected to a subreport. Monitoring the two datasets, I did
    not see anything that would cause this kind of slowdown when using this
    setup. There must be something else going on here. Is the main
    report's master, always the same value? How is this retrieved?

    If you switch to MagicSQL linking do you see any speed improvement?

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited June 2014
    On 10/06/2014 17:58, Nico Cizik (Digital Metaphors) wrote:

    When working in the Designer the main reports selection criterion (on
    master table primary key) is hard coded; when printing from print menus
    in the main area of our application the TdaCriteria for that looks after
    this selection value is dynamically assigned.


    No, if anything, its worse. If I take the SQL for the detail query and
    run it outside of RB, manually setting the main selection criterion that
    RB would set dynamically it executes in an about 300ms on the database
    that I'm using for writing the report. My hunch is that the latency is
    nothing to do with data retrieval but the work the report engine has to
    do to render the pages and that somehow a huge overhead is introduced
    when this is done via a subreport compared to the main report. But its
    only a hunch. Would love to send you the report but of course its not
    viable as everything is setup to work in a very specific environment.
    Not sure how to proceed?
  • edited June 2014
    On 10/06/2014 18:29, Paul Toms wrote:

    OK, hold fire for a bit...may have found something. Will pick later if
    have more news... :)
  • edited June 2014
    On 10/06/2014 18:41, Paul Toms wrote:

    Update: the bottle neck seems to be actually caused by third level
    TdaQueryDataViews that are data pipelines for subreports nested within
    the subreport that I originally was referring to. These
    subreports/querydataviews are also parameterized and unlike the outer
    subreport are going to fire about 20 times in this particular data
    scenario. So this report takes about 140 seconds to render its 27 pages
    (the inner subreports are mostly returning no data).

    However, I can get the report to run in about 20 seconds if I make these
    third level TdaQueryDataViews "manually" joined, and set a dummy search
    criterion on each TdaQueryDataViews. Then in the main report I can setup
    these TdaCriteria for each of the subreports/querydataviews:


    procedure ReportBeforeOpenDataPipelines;
    VAR
    lMasterSQLBuilder, lChildSQLBuilder: TdaSQLBuilder;
    lMasterCriteria, lChildCriteria: TdaCriteria;
    begin
    lMasterSQLBuilder := TdaSQLBuilder.Create(Jobs);
    lMasterCriteria :=
    lMasterSQLBuilder.SearchCriteria[lMasterSQLBuilder.SearchCriteria.IndexOf('Jobs',
    'Jobno')];

    lChildSQLBuilder := TdaSQLBuilder.Create(CrewLinkedToEql);
    lChildCriteria :=
    lChildSQLBuilder.SearchCriteria[lChildSQLBuilder.SearchCriteria.IndexOf('Crew',
    'Job_no')];
    lChildCriteria.Value := lMasterCriteria.Value;
    lChildSQLBuilder.ApplyUpdates;
    lChildSQLBuilder.free;

    lChildSQLBuilder := TdaSQLBuilder.Create(TransLinked);
    lChildCriteria :=
    lChildSQLBuilder.SearchCriteria[lChildSQLBuilder.SearchCriteria.IndexOf('Tranplan',
    'Job_number')];
    lChildCriteria.Value := lMasterCriteria.Value;
    lChildSQLBuilder.ApplyUpdates;
    lChildSQLBuilder.free;

    lMasterSQLBuilder.Free;
    end;

    Whether or not this is a flaw in RB or whether its just how it is with
    parameterized views I cannot say. But thought I'd post this anyway in
    case there is anything that can be done without having to resort to the
    ReportBeforeOpenDataPipelines() code as that only works in this
    circumstance because the subreports/querydataviews pull their data from
    tables have fields that are common to the master querydataview.

    Thanks, Paul
  • edited June 2014
    Hi Paul,

    This is not a flaw in ReportBuilder, but rather how parameterized
    queries function. When you changed to DataPipeline (MagicSQL) linking,
    you were still likely using parameterized linking for your other queries
    which was slowing things down (due to the fact that the detail is
    re-accessed for every master). This is why we created DataPipeline
    linking and MagicSQL.

    If you would like to see the difference is speed between DataPipeline
    linking and parameterized linking, take a look at example 64 of the main
    reports demo.


    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited June 2014
    Thanks Nico,

    My "manual link" workaround with a search criterion populated from the
    main report in its ReportBeforeOpenDataPipelines() event is a nice
    workaround that I'm happy with. Thanks for the help :)


This discussion has been closed.