---------- Original Message -----------
From: "y_ongky_s" <ongky74@...>

> So the procedure work by deleting old records and then create new
> records with insert command.
>
> The problem is after records deleted from stock card table it create
> garbage collection and slow down
> the process when the procedure start to rebuild the stock card by
> inserting new records into it.
>
> Is there any command that I could use to disabled garbage collection
> before I start
> the recalculation process?

------- End of Original Message -------

See http://www.firebirdsql.org/manual/gfix-housekeeping.html for instructions
on running manual sweeps, and disabling automatic sweeping.

Note that performance can suffer during a sweep (cooperative, in particular)
because the cleanup process takes time, directly as part of your query, but
also during operations where sweeping has been disabled or not yet performed
but the garbage remaining in the database actually slows things down.

An example: a large operation changes a lot of indexed fields, leaving behind
back-versions of records, but also lots of entries in the indices to those back-
versions (indexed by old value). GC will be invoked on the records if you do a
full-table-scan, but not on an indexed-scan -- but the index-scan performance
is still impacted by the index containing a bunch of cruft, sending it looking
at records that couldn't possibly be interesting. Once the background GC runs
and cleans up both the records and the index entries, performance improves.

The reasoning behind automatic GC is that, while it slows some operations down
at first (one-time cost), it speeds many others up later. Can you tell which
aspect is really slowing you down? After your inserts, are selects initially
slow, then faster? And you're sure it's a GC issue, not an indexing issue, or
the cost of cacheing data into memory?

There may be a mode where FB doesn't bother creating back-versions of records
for other transactions to read, but that probably involves single-user-mode and
other restrictions you're not going to like. That's because it's not enough to
be the only user at the beginning of your update; there's always a chance that
midway through your transaction, someone could connect and FB would want to be
able to serve up the old data (delete hasn't been committed yet) to that
transaction. So the expectation that some process might, at some point, need
the data leads FB to track it. You'd need to kill that at the source. I can't
find docs to indicate that even in 'single' shutdown mode, MVCC is fully
disabled.

You might try the following to avoid creating garbage in the first place,
without changing any settings.

Dump your recalculated data into a temporary table, then perform selective
insert/update/deletes against the persistent table only where absolutely
necessary. (You can do this without a temporary table, but the multiple
recalculations may hurt you.) Firebird does support the MERGE INTO statement,
but it doesn't have SQLServer's 'delete' functionality, so you can't do it all
in one pass. I'm also pretty sure that MERGE INTO still 'touches' records that
it finds matches for, even when no changes are truly made, and that would
create some garbage you want to avoid (very tiny delta records which have the
effect of holding a lock for you on the rows you might have wanted to update
but weren't actually different.)

So you'd need to instead do something like:

create global temporary table Y (...) on commit delete rows;
create unique index ix_Y_pk on Y (pk);
-- create your 'new' dataset
insert into Y ...
-- delete minimal set
delete from X where not exists (select * from Y where Y.pk = X.pk);
-- insert minimal set (could be part of merge-into, in theory)
insert into X select * from Y where not exists (select * from X where X.pk =
Y.pk);
-- update only where truly different
merge into X using (select * from Y) as Y on Y.pk = X.pk and (X.a is distinct
from Y.a or X.b is distinct from Y.b or X.c is distinct from Y.c)
when matched then update X set X.a = Y.a, X.b = Y.b, X.c = Y.c ... ;

-Philip