Board index » delphi » Master/Detail with CachedUpdates

Master/Detail with CachedUpdates

Hi!

Some time ago I had to manage, in CachedUpdates, two (or more) Master/Detail
TQuerys. Using the Detail.Datasource was not enough, because everytime the
Master scrolls the record, the native TQuery closes and opens again then
Detail (loosing all the Cached records).
So I tried to use, instead of the Detail.DataSource, the Detail.Filter:
every time the Master scrolls, I change the filter of the detail in order to
be the actual "detail" of the Master query, without closing the detail
dataset.
As I need to invoke ApplyUpdates at "user decision", it seems i need to keep
the detail dataset open.
I cannot rely on TDatabase.StartTransaction because i can have multiple
windows with multiple DataSet in different states, all connected to the same
TDatabase (when the user "saves" the changes of a window, I call
subsequently: TDatabase.StartTransaction, MasterTQuery.ApplyUpdates,
DetailTQuery.ApplyUpdates, MasterTQuery.CommitUpdates,
DetailTQuery.CommitUpdates, TDatabase.Commit - except TDatabase.Rollback).

Now... the problem is that with huge tables the filtering operation is
terribly slow.

Is there any way to solve my terrible problem ?!

Thanks a lot to everyone!

--

Andrea Casati
-->> 'DEL' must be erased from address (anti-SPAM) <<--

 

Re:Master/Detail with CachedUpdates


Quote
On Thu, 16 Sep 1999 08:51:56 GMT, "Syd" <DELswdeve...@kona.it> wrote:
>Hi!

>Some time ago I had to manage, in CachedUpdates, two (or more) Master/Detail
>TQuerys. Using the Detail.Datasource was not enough, because everytime the
>Master scrolls the record, the native TQuery closes and opens again then
>Detail (loosing all the Cached records).
>So I tried to use, instead of the Detail.DataSource, the Detail.Filter:
>every time the Master scrolls, I change the filter of the detail in order to
>be the actual "detail" of the Master query, without closing the detail
>dataset.
>As I need to invoke ApplyUpdates at "user decision", it seems i need to keep
>the detail dataset open.
>I cannot rely on TDatabase.StartTransaction because i can have multiple
>windows with multiple DataSet in different states, all connected to the same
>TDatabase (when the user "saves" the changes of a window, I call
>subsequently: TDatabase.StartTransaction, MasterTQuery.ApplyUpdates,
>DetailTQuery.ApplyUpdates, MasterTQuery.CommitUpdates,
>DetailTQuery.CommitUpdates, TDatabase.Commit - except TDatabase.Rollback).

>Now... the problem is that with huge tables the filtering operation is
>terribly slow.

>Is there any way to solve my terrible problem ?!

>Thanks a lot to everyone!

Borland announced this problem in Delphi 5 readme.txt :

Cached updates and master-detail relationships
-----------------------------------------------
[Enterprise and Professional editions]
Cached Updates cannot be used effectively with TQuery components when
doing master-detail links (DataSource property). This is because
the detail query is reexecuted each time the master record pointer
moves.

I think it is absolutely ridiculous myself, defeats master detail
connectivity completely.  I found out about it in Delphi 3, after
tearing my hair out trying to find why my detail was clearing after
apply master updates.

One solution is to effectively connect the detail to the master by
reassigning the detail parameters in the OnAfterScroll event of the
master query.  Make sure you apply both the master and the detail
updates on the master's OnBeforeScroll.  One word of warning if you
are using a DBGrid, use a flag to prevent Updates when opening the
master, because as the grid fills up, it will call
OnBeforeScroll/OnAfterScroll many times whilst opening.  

Oh and don't use filters they are extremely slow and inefficient.

Paul
aspsc...@tcp.co.uk
^^ remove 'as' anti spam prefix to reply

Re:Master/Detail with CachedUpdates


Paul Scott ha scritto nel messaggio ...

Quote
>On Thu, 16 Sep 1999 08:51:56 GMT, "Syd" <DELswdeve...@kona.it> wrote:

>>Hi!

>>Some time ago I had to manage, in CachedUpdates, two (or more)
Master/Detail
>>TQuerys. Using the Detail.Datasource was not enough, because everytime the
>>Master scrolls the record, the native TQuery closes and opens again then
>>Detail (loosing all the Cached records).
>>So I tried to use, instead of the Detail.DataSource, the Detail.Filter:
>>every time the Master scrolls, I change the filter of the detail in order
to
>>be the actual "detail" of the Master query, without closing the detail
>>dataset.
>>As I need to invoke ApplyUpdates at "user decision", it seems i need to
keep
>>the detail dataset open.
>>I cannot rely on TDatabase.StartTransaction because i can have multiple
>>windows with multiple DataSet in different states, all connected to the
same
>>TDatabase (when the user "saves" the changes of a window, I call
>>subsequently: TDatabase.StartTransaction, MasterTQuery.ApplyUpdates,
>>DetailTQuery.ApplyUpdates, MasterTQuery.CommitUpdates,
>>DetailTQuery.CommitUpdates, TDatabase.Commit - except TDatabase.Rollback).

>>Now... the problem is that with huge tables the filtering operation is
>>terribly slow.

>>Is there any way to solve my terrible problem ?!

>>Thanks a lot to everyone!
>Borland announced this problem in Delphi 5 readme.txt :

>Cached updates and master-detail relationships
>-----------------------------------------------
>[Enterprise and Professional editions]
>Cached Updates cannot be used effectively with TQuery components when
>doing master-detail links (DataSource property). This is because
>the detail query is reexecuted each time the master record pointer
>moves.

>I think it is absolutely ridiculous myself, defeats master detail
>connectivity completely.  I found out about it in Delphi 3, after
>tearing my hair out trying to find why my detail was clearing after
>apply master updates.

>One solution is to effectively connect the detail to the master by
>reassigning the detail parameters in the OnAfterScroll event of the
>master query.  Make sure you apply both the master and the detail
>updates on the master's OnBeforeScroll.  One word of warning if you
>are using a DBGrid, use a flag to prevent Updates when opening the
>master, because as the grid fills up, it will call
>OnBeforeScroll/OnAfterScroll many times whilst opening.

>Oh and don't use filters they are extremely slow and inefficient.

>Paul
>aspsc...@tcp.co.uk
>^^ remove 'as' anti spam prefix to reply

BUT I *don't* want to reassign the detail parameters AS it implies closing
the detail AND, therefore, clearing the cache.
OR
am I doing wrong?

--
Andrea Casati
-->> 'DEL' must be erased from address (anti-SPAM) <<--

Re:Master/Detail with CachedUpdates


Quote
On Thu, 16 Sep 1999 22:20:33 GMT, "Syd" <DELswdeve...@kona.it> wrote:
>>One solution is to effectively connect the detail to the master by
>>reassigning the detail parameters in the OnAfterScroll event of the
>>master query.  Make sure you apply both the master and the detail
>>updates on the master's OnBeforeScroll.  One word of warning if you
>>are using a DBGrid, use a flag to prevent Updates when opening the
>>master, because as the grid fills up, it will call
>>OnBeforeScroll/OnAfterScroll many times whilst opening.

>>Oh and don't use filters they are extremely slow and inefficient.

>BUT I *don't* want to reassign the detail parameters AS it implies closing
>the detail AND, therefore, clearing the cache.
>OR
>am I doing wrong?

Unfortunately, that is why I said you have to apply the detail updates
in the OnBeforeScroll event, so you can close it to reassign the
parameters.  I don't think there is any other way.  I could be wrong.

Paul
aspsc...@tcp.co.uk
^^ remove 'as' anti spam prefix to reply

Other Threads