On Wed, Oct 10, 2012 at 1:20 AM, y_ongky_s <ongky74@...> wrote:

>
> I have a stored procedure used to recalculate stock card on my application.
>
> The procedure is counting by date. I mean if the user recalculate
> from beginning of month than if there are data already in the
> stock card table will be deleted and then the procedure will
> insert record into the stock card date by date until current date.
>
> After deleting record the firebird will increase garbage collection which
> slowing down the process. Is it true?
>

It's a little hard to tell. If you really mean "stock card table will be
deleted", dropping a table doesn't cause garbage collection. If you're
deleting or updating records, yes you are creating new versions of records.
When those versions are mature - meaning that no running transaction needs
the older version - the old versions become "garbage" and will cause
garbage collection. Yes, garbage collection slows down the system, but not
as much as letting old versions build up, filling pages with useless stuff.


My question is how to speed up the process. I mean is it possible run the

> process without any transaction active that will create garbage collection.
>

As long as you never delete or update records, you'll never have garbage
collection. I don't understand exactly what you're doing, so this may
make no sense. To avoid garbage collection, you might try keeping the old
card stock records and just creating new ones. You could create a new card
stock table every month or quarter or year and have a view like
'CurrentCardStockView" that points to the most current table. When an old
table ceases to be interesting, drop it.

Good luck,

Ann

For those who haven't sat through the record version lecture and wonder how
a delete could cause a new version of a record, remember that versioning is
used for snapshotting, concurrency control, and failure recovery. So a
delete can't just get rid of a record. Instead, it creates a very small
record saying "this record is deleted" and pointing to the next older
record version. When the delete is mature, garbage collection will remove
the old versions and the deleted stub.


[Non-text portions of this message have been removed]