TadoDataset with child
I am revisiting the use of TadoDatasets and ReportBuilder. Some time ago, I
found reports with paramertized queries (master/detail) were extremely
slow. The solution, as I recall, was to select ALL child records and order
that result set by the parameter value. Because there are a hundred thousand
records in the child table, I abandoned TadoDatasets in favor of Ttables
(which my database also supports).
Because we want to open reports to end users, including modification of
queries, I want to use parameterized queries (eg, select * from Orders where
CustomerID = :CustomerID).
I tested on a pretty simple report but it takes 40 secs to generate 15
pages. Yikes!
The last reading I have on this topic is a TechNote authorized by Nard dated
Wed, 12 Apr 2006 with the subject " master detail crawls at slow speed".
Perhaps there is some newer thinking on this?
Thanks!
Sher
found reports with paramertized queries (master/detail) were extremely
slow. The solution, as I recall, was to select ALL child records and order
that result set by the parameter value. Because there are a hundred thousand
records in the child table, I abandoned TadoDatasets in favor of Ttables
(which my database also supports).
Because we want to open reports to end users, including modification of
queries, I want to use parameterized queries (eg, select * from Orders where
CustomerID = :CustomerID).
I tested on a pretty simple report but it takes 40 secs to generate 15
pages. Yikes!
The last reading I have on this topic is a TechNote authorized by Nard dated
Wed, 12 Apr 2006 with the subject " master detail crawls at slow speed".
Perhaps there is some newer thinking on this?
Thanks!
Sher
This discussion has been closed.
Comments
Then this would probalby not normally be posted under .RAP :-)
don't know how many records you have, or what events you have tied to the
tables (e.g. AfterScroll even may slow things down).
I don't have enough info on your UI, but if you are not using RB's DADE, and
you are looking for purly speed, create TLists (or most probalby
TObjectLists) of your data and use TJIPPipelines. I've done this and it
makes things a lot faster--none of the overhead of the TDataset.
Ed Dressel
Team DM
sher
Selecting All records is slow, we do not recommend that. I think you
misunderstood.
Most SQL database engines only support SQL. Using a Table object, results in
a Select All records.
Best solution is to use DADE, which can generate special linking SQL
automatically for you.
When using external datasets, you can write your own SQL to support
DataPipeline linking. Using DataPipeline linking requires that you select
all the detail records that will be used in the report. For any search
criteria in the master, you have to also apply the criteria to the detail -
DADE does this for you.
--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com