jasajona wrote:

>
> What I figured out is:
> Before this query I delete tons of records. After 20 000 by default fb
> starting sweep.

No, Firebird does a sweep every 20 thousand transactions, not every 20
thousand records. A transactions starts with a[n implicit] set
transaction and ends with a commit or rollback [retaining]. Despite the
common usage a record update is not equivalent to a transaction. What
you're running into is garden variety garbage collection. All the
deleted records are being removed from the database - their data and
their index entries. The real problem is index entries, particularly
since that table has an index with lots of duplicates. Removing
duplicates is typically an n factorial operation. Find that index, drop
it, and replace it with an index with the same key plus the primary key
in that order.

Regards,


Ann