At 03:49 PM 11/01/2005 +0000, you wrote:

> > 2) If you were in my software team I'd send you away with a flea in
> > your ear and tell you to write a decent stored procedure.
>
>:) I understand that, but even with this First() function query works
>fast enough. So firebird if faster than old dos app written on Pascal :)
>
>What I figured out is:
>Before this query I delete tons of records. After 20 000 by default fb
>starting sweep. And it is slowing down this query that bad. So far it
>is not final source of this problem. I turned off auto sweep, but this
>happening again. At some moment cpu load jump to 100% and stay this
>way all the time, like doing sweep. I cancel and start again without
>deletion just select, sweep starts again. Is this possible?

If in fact it is sweep running, then clearly you have a problem in your
software with uncommitted long-running transactions. I'd want to address
this one as a priority, before starting to think you're looking at an
engine bug when some query starts slowing down.

GC and sweep are two different processes. GC goes on all the time (if it
can), whereas sweep either kicks in automatically (if autosweeping is
enabled) or must be done manually, via gfix, if you disabled
autosweep. You can run a manual sweep any time.

You have to get rid of garbage some way; so the garbage collector is going
to want to do its stuff whenever it can. How and when the GC does its
stuff depends on whether you are using superserver or classic. In SS, the
GC thread tries to start when there is a "window of opportunity"; in
Classic, the GC hits the first query after a big operation on the involved
table(s).

Autosweep kicks in when the gap between the oldest transaction and the
oldest active transaction surpasses the sweep interval, if the sweep
interval is > 0. If your system really is doing autosweep with a sweep
interval of 20000, that tells you that you need to take a hard look at what
your applications are doing with regard to transaction life.

In the short term, until you get your applications fixed up, I'd suggest
running manual sweeps frequently. How frequently depends on how fast you
are building up this garbage. Use gstat -h to monitor the gap. And, if
you are doing massive deletes as a matter of course, make it routine to run
a manual sweep afterwards.

./heLen