--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "kaczy27" <kaczy27@>

wrote:

> >
> > Hi,
> >
> > I am experiencing great performance loss of the database after
> > rolling back transactions.
> >
> > I am doing quite a lot of those - in my testing module, which is
> > roughly following this patter, 1. get prepared database, 2.

start

> > transaction 3. verify result correctnes, 4. rollback
>
> By rollback, do you mean that you are deliberately calling

rollback on

> the transaction or simply abandoning the transaction.

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)

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

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

>
> Until they are cleaned up, deleted records are still in all the
> indices etc and so will still affect performance. You can try and

run

> a select count(*) from a table you have just run a lot of changes

on

> (after the rollback in a new transaction obviously) which will

force

> the cleanup to happen at that time.

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.

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

>
> Adam
>

Thanks,

CUIN Kaczy