---------- Original Message ----------- From: "y_ongky_s" <ongk...@gmail.com> > 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
