At 10:29 AM 7/06/2004 +0000, you wrote:

>Hi,
>
>The firebird gurus doesn't seem to thrill to this subject :-(
>
>Anyway, I didn't find any paper / post that could answer my question.
>
>Any hint?
>
>Best regards,
>Fabrice Aeschbacher
>
>
>--- In firebird-support@yahoogroups.com, "Fabrice Aeschbacher"
><fabrice.aeschbacher@s...> wrote:
> > Hi,
> >
> > (LI-V6.3.0.4290 Firebird 1.5 Classic / linux)
> >
> > I noticed, using gstat -r, that the total versions / max versions
> > records for
> > one table is allways growing. So I started some tests, to try to
> > understand how it works:
> >
> > CREATE TABLE A (
> > ID INTEGER,
> > V INTEGER
> > );
> >
> > INSERT INTO A ( ID, V ) VALUES ( 1, 0 );
> > COMMIT;
> >
> > Then I wrote a little C program that does the following:
> >
> > connect_to_db();
> > i = 1;
> > while (true) {
> > start_transaction();
> > sqlexec: UPDATE A SET V = :i WHERE ID = 1;
> > i++;
> > commit_transaction();
> > }
> >
> > Here is the output of gstat, before the test is started:
> >
> > A (172)
> > Primary pointer page: 548, Index root page: 551
> > Average record length: 9.00, total records: 1
> > Average version length: 0.00, total versions: 0, max versions: 0
> > Data pages: 1, data page slots: 1, average fill: 1%
> >
> > And during the test:
> >
> > A (172)
> > Primary pointer page: 548, Index root page: 551
> > Average record length: 13.00, total records: 1
> > Average version length: 9.00, total versions: 47528, max versions:
> > 47528
> > Data pages: 661, data page slots: 661, average fill: 92%
> >
> > As we can see, the record versions never stops growing.
> > And the UPDATEs are taking more and more time.
> >
> > When I stop the test program, the number of versions stops growing, but
> > is not reset.
> >
> > When I restart the test program, the number of versions is not
>reset, and
> > keeps growing.
> >
> > So the questions:
> > - How can I prevent this?
> > - What would be the correct way of building an application that has to
> > frequently update the same records with different values?
> >
> >
> > One more remark: when I change the test program and make it
> > disconnect/reconnect
> > every 1000 transactions, then the number of versions grows up to 1000,
> > is reset to 0 after the reconnection (in fact, after the first UPDATE
> > after the
> > reconnection), and restarts growing.
> > Does this mean that I must regularly force a reconnection?

Apparently it does. :-) That is, if you plan to write an application that
just updates the same record thousands and thousands of times.

On the other hand, if your application updates multiple records in
different tables, then garbage collection will clean up the old record
versions and you won't see this slowdown you get by hammering the same
record over and over.

/heLen