Slow Subreport Architecture (v15.04)

rbuser

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

nicocizik

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?

rbuser

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?

rbuser

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...

rbuser

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

nicocizik

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.

rbuser

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