On Thu, Nov 28, 2013 at 3:41 AM, Thomas Steinmaurer <ts@...> wrote:

the same statement with the same execution plan is running much slower
on different occasions? Co-operative garbage collection in Firebird
Classic and SuperClassic might be the root cause.

Check out this demo how to get some feedback if garbage collection has
been additionally performed for an executed statement:

http://www.iblogmanager.com/download/demos/fbtm/fbtm_30_garbage_collection.htm

Err ... sure, running with co-operative garbage collection makes the cost of 

garbage collection show up in the cost of running a statement.  But the actual

cost of garbage collection is about the same for co-operative and thread-based

methods; the difference is that the thread-based doesn't show up as the cost

of a user's statement.  In a single user system with long pauses between

statements, the thread-based garbage collector is a big win because it runs

in the pauses.  In a heavily loaded system where there is little or no idle time,

the thread-based collector competes with useful work.

As you know, when Firebird executes a delete statement, it actually creates

a new short version of the record called a deleted stub.  Once the delete is

mature - meaning that no transactions expect to read the records that have

been deleted - both the stub and the original version are removed.  So the

select count(*) does more work than the delete that preceded it.

One question is why you had to disconnect to force garbage collection...

Cheers,

Ann