> I am deliberatelly calling RollBack transaction. See the stats I've
> provided, the transaction ids are in sync (I am not sure whether
> there is a proper 1 point difference or a 2 points but that should
> not matter that much)

Good. in this case the garbage should not be abandoned. Calling
Rollback will undo the changes of every query the transaction has run
and then flag the transaction as committed.

This article by Ann Harrison is important to understand from a Garbage
buildup-collection perspective.

http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_expert4

>
> > IIRC, then calling rollback will attempt to reverse every
> instruction
> > that was run which will take a lot longer than abandoning it, but
> > should not leave its mess around for someone else to clean up.
> There
> > is a limit to the size of this "undo log", or the server would run
> out
> > of memory. Once this limit is reached, a rollback simply abandons
> the
> > transaction. From memory the limit is somewhere around 5000 -
> 10000.
> 5000-10000 of what?

Inserts, updates, deletes, (including those run implicitly by triggers
etc on tables). Basically the aim of a rollback is to undo whatever
the transaction has done. There are two ways it can do this. Either it
is abandoned in which case the next transaction to hit a record
notices that there is an uncommitted record version of the record in
question by a rolled back transaction. That transaction has to incur
the cost of the cleanup (co-operative garbage collection). The other
way is that the transaction can deliberately undo everything it has
done, then it can be committed.

>
> > But from your description this is unlikely (although a lot can be
> done
> > in 2-3 minutes).
> I've done around 500 transactions during that time (taking this
> number from stats)

i am talking about the number of inserts/updates/deletes in the
transaction that you roll back. I mentioned above there is an undo
log. If Firebird was to track forever the transaction, you would
eventually run out of RAM or disk space or both if your transaction
went for long enough. Also the action of rolling back would take an
awful long time. So at a particular number of inserts/updates/deletes,
the undo log is not maintained, and any rollback is simply a case of
abandoning the transaction.

> The problem is definitelly in indices, since that base is
> effectivelly empty and the indices indicate wrong statistics. I
> think that this is a reason for performance lossess. As I said
> backup/restore is a cure-all.
>

To recalculate the statistics of an index, you can call 'SET
STATISTICS [indexname]'. Alexandre posted a Stored Procedure the other
day which would recalculate the statistics on ALL indices, search the
archives.

> >
> > You could alternatively run a sweep, or even just backup the
> database
> > will force garbage collection unless you choose otherwise.
> How to perform a sweep?

Use the command line tool gfix (-sweep). Do gfix /? for details.

Adam