--- In firebird-support@yahoogroups.com, Tetram Corp <dev@...> wrote:

>
> hi,
>
> i made a program in Delphi (using IBX) with FB1.5 and during a "while
> not eof" loop, each X fetches, my application freeze for 60s (neither
> more nor less)
>
> in pseudo code, my loop is:
>
> SQL = "SELECT FROM aView" (neither group by nor join, just cast and
> substring on a single table)
> while not EOF do begin
> if DoSomeSelectInsertUpdateExecuteProc then CommitRetaining
>
> NEXT
> end
> (All in the same transaction)

How can that be true when you have a commit (albeit a commit retaining).

>
>
> is it a known problem?

Commit retaining blocks garbage collection. My guess is your table has
a lot of garbage to be collected, and 1.5 doesn't do a very good job
here (or rather 2.x does a much better job) when you have indices on
that table with poor selectivity (lots of duplicates in the field).

> is there a solution? (except having vacations :) )

A backup-restore cycle will immediately solve the problem if it is
garbage related, but that is curing the symptoms not the disease.
Upgrading to 2.x might minimise the symptoms. At the end of the day
however, you need to handle transactions well to avoid garbage. That
means using commit instead of commitretaining unless you actually need
it. Avoid unnecessarily long transactions. It means running a sweep
periodically and a backup nightly.

Adam