Adam a écrit :

> --- 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).
>
>

it is the goal of a commitretaining: commit last changes but keep the
transaction started

>> 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
>

The problem is that the table used by the view is newly filled (in a
newly created database) by a process just before my loop

Thierry