Why transactions and lookup fields are so slow together?

[Bill Todd (TeamB)]

I would suggest not using cached updates and transactions together with
Paradox tables.

--
Bill

Bill Todd (TeamB)
(TeamB cannot respond to questions received via email)

[Attila Ambrozai, Jr.]

I have a table which have three lookup fields and which
table have its CachedUpdates set to true. After I start a
transaction and start to make changes on that table the
operations on the table becomes more and more slow.
For instance after 30 changes since started the transaction
any new operation on that table takes around 4 seconds
(with a Celeron 466 machine!). What is the reason of this?
What is the solution? Please help me. Thank you in advance.

Attila Ambrozai, Jr.
Hungary
am...@ambro.hu

[Attila Ambrozai, Jr.]

OK, I will try to find other solutions. Thanks.

Attila

Bill Todd (TeamB) <bi...@dbginc.com> wrote in message
news:87d4p7$4t...@bornews.borland.com...

[Philip Cain]

Atilla,

Paradox is not the issue here. The problem is in the local cache that
Delphi makes to handle cached updates. This local cache is a file that
mimics the dataset and cached updates mixes that with what is fetched
from the database.

When you say ApplyUpdates and CommitUpdates, Delphi and the BDE use
this cache to send data to the db. But when that is finished, the
cache file does not go away. Records are marked done and are not used
again, but the file is still there. As you make more changes, Delphi
adds to the file and the next time you say ApplyUpdates, only the
changed records are used to update the database.

The problem with this approach is that the cache is not indexed and so
every time Delphi needs information from it, it must read the cache as
a simple sequential file. The bigger the file, the more time it takes
to read it, and that is what you are seeing.

Delphi/BDE can't close the file as long as you are using the same
fetch from the db. If it did close the file, you would lose sight of
your changes. Remember that cached updates is a one-way street from
your client machine and dataset to the database. There is no mechanism
for returning the data from the database to the dataset that you have
open.

The only way to get the data into the dataset is to close the TQuery
or TTable and open it again. That process will erase the local cache
and open a new one. So after every transaction, you should close and
open the datasets involved. That will solve the speed problem.

Phil Cain
--