Onky,

> 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.
>
> Is there any another way to get only one record directly.
>

Unfortunately, no. Firebird has two ways of using an index. One reads the
index, finds all qualifying rows, sets bits represent those record
numbers, then does the same for any other indexes that can be used,
combines the bitmaps and reads the records in storage order. The other
method reads the index, finds the first match, finds that record, returns
it, then reads first the index and then the data to get the next record.
The first method is efficient and can use multiple indexes on a single
query but it does not return rows in index order. The second method is
what you need for a FIRST 1 (aka limit) query. The two methods cannot be
combined.

In this case, it's probably smart to qualify rows by date and code, then
sort the results by id and return the first - certainly better than
reading the id field in order and throwing out all the results that don't
match by date and code.
Firebird is optimized for returning sets of rows ... is there a way to ask
for more than one at a time?

Good luck,

Ann


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