Hello Ann,

Thanks for your reply.

I use stock card table to store the stock transaction from the beginning application used so after one year the stock transaction
will create hundred thousand records in the table.

If I recalculate the stock card from the beginning of the year
with the procedure I have right now than it will delete
records from the beginning of the year that will create about hundred
thousand deleted records before the procedure start to recreate the stock card.

And thanks for asking me to make sure that the problem is garbage collection.
I analyse again the procedure and found that the statement

SELECT FIRST 1 ID
FROM STOCK_CARD
WHERE Code = 'BBOT0001' AND
Date <= '02.01.2012'
ORDER BY ID DESCENDING

will make firebird to scan all the record on the table that
match the criteria not directly found the first record that
match the criteria even i have create an index in descending order.

Supposed i have stock card like this:

Code Name No_Dok Date Tp_Tran ID
BBOT0001 Bottle TB/GBB/1201/001 01.01.2012 D 1
BBOT0001 Bottle TB/GBB/1201/002 02.01.2012 D 2
BBOT0001 Bottle KB/GBB/1201/001 03.01.2012 K 3
BBOT0001 Bottle KB/GBB/1201/002 04.01.2012 K 4

and than I found that user insert a stock transaction on date 02.01.2012
than I must insert the new record between ID 2 and 3.
I have to know what ID max for transaction on date 02.01.2012.
I used the statement

SELECT FIRST 1 ID
FROM STOCK_CARD
WHERE Code = 'BBOT0001' AND
Date <= '02.01.2012'
ORDER BY ID DESCENDING

It will return 2.

I already create an index with field Code+Date in Descending order.

but I found that using the above statement Firebird will scan
all records that match the condition first than it will return
only one records I want.

Is there any another way to get only one record directly.

Regards,

Ongky

--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote:
>
> On Thu, Oct 11, 2012 at 2:20 PM, Ann Harrison <aharrison@...>wrote:
>
> > Ongky,
> >
> >
> >> 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.
> >
> >
> Are you absolutely certain that the problem is garbage collection?
>
> Good luck,
>
> Ann
>
>
> [Non-text portions of this message have been removed]
>