Just correcting myself a little bit. I said...

> in most situations I prefer everything to be saved or nothing.

What I have left out is rollback after a problem. So it really should
be something like...

transaction.BeginBusy(true)
try
try
do everything
transaction.Commit;
except
transaction.Rollback;
raise;
end;
finally
transaction.EndBusy;
end;

ensuring that whatever happens the transaction will be closed when the
process is finished. It does not really matter whether the try/except
is inside the try/finally or vise versa (I think inside may save one
cursor flicker ;-)

> Thanks Geoff, that's cleared thatup for me. The reason I do the
> intermediate commits is that it was suggested that with high
> iteration loops like I'm using that the cache may become
> overloaded and cause the app to slow down again. I did,
> originally, only have the one at the end.
> Do you have any view on that?

What "cache"? Perhaps you have been using cached updates in a prior
situation?

I guess that Interbase/Firebird probably does have some overhead, and
there may be "Oldest Active Transaction" issues if the process runs
for many hours. But I have done large numbers of inserts without
either the application or the server getting into a bind. The only
real impact is, if the the application fails and the transaction needs
to be rolled back. It appears that IB takes an optimistic approach to
changes, expecting that they will be commited. So a commit is almost
always quite fast, whereas rollback may take a long time.

> In any case, unless the app actually fails there is no way
> that it can be stopped in the middle of the loop so the
> entire thing will be saved each time.

I am sure it will stop if I turn the computer off :-)


Geoff Worboys
Telesis Computing