Subject Re: Firebird Hanging
Author Adam
> I do have a nightly routine schedule with the Windows scheduler that
runs a
> backup, copies the backup file to another PC on the network and then
runs a
> sweep.

That is fine then, Ann has already debunked my garbage collectino
theory as to why SS may be faster.

> I believe this customer is prone to Task-Manager-ending my
application if
> something is running too long (as opposed to calling me to report the
> slowness). I do have a couple of reports that need optimizing.
> What happens to their connection when the app is ended in this way?

One of two things happen. Either the transaction is rolled back nicely
or it is aborted.

Hopefully it rolls back nicely. This involves the database line by
line undoing everything you had done so far in your transaction until
the database state is what it was when you started your transaction.
It would then commit your transaction (because that is cheaper than a
rollback from a garbage perspective).

What can happen however is the connection and transaction objects are
destroyed before all that can happen. This leaves the uncommitted
records in the database. At some point in time, Firebird recognises
that a connection is dead and marks it as rolled back. The uncommitted
records are subsequently garbage collected at the expense of the next
poor guy who happens to want to read the value.

Due to the index structure of FB 1.5 and earlier, this cleanup can
take a long time which may be perceived as 'hanging'. But you have
identified that you perhaps have an inefficient report or two, which
may be a good place to start. Sometimes, the underlying database
design does not have the indices that would assist in a report.
Occasionally it does but the optimiser gets it wrong but it is not too
hard to trick it into using an optimal plan.