Subject Re: [firebird-support] Another ordering question?
Author Tim Ward
Well, with all transaction numbers within a few hundred of each other

    Oldest transaction    391428664
    Oldest active        391428665
    Oldest snapshot        391428665
    Next transaction    391429145

it isn't noticeably better

   Elapsed time= 0.01 sec
   Buffers = 150
   Reads = 212
   Writes 0
   Fetches = 546

so that doesn't look like the explanation ...

On 09/09/2015 13:13, Nick Upson nu@... [firebird-support] wrote:
without a "where" clause firebird has to read the entire table (to determine if each row is valid for the current transaction) and sort the results

Nick Upson, Telensa Ltd, Senior Operations Network Engineer
direct +44 (0) 1799 533252, support hotline +44 (0) 1799 399200

On 9 September 2015 at 13:00, Tim Ward tdw@... [firebird-support] <> wrote:



Current memory = 3437420
Delta memory = 152
Max memory = 3586952
Elapsed time= 0.04 sec
Buffers = 150
Reads = 232
Writes 0
Fetches = 132082

There may be several thousand records in the table, but the primary key
is defined as (MYKEY1, MYKEY2) and the plan says it's using it, so why
the 232 disk reads? - one might have guessed 2 would be enough, one for
the index and one for the record. (The key fields are both 32 bit
integers. Yes, I do have plans to replace them by a generator-generated
key, but I can't see why that would make a difference to this particular
performance issue.)

This table is being used as a FIFO queue. Would the most likely
explanation be lack of garbage collection, so it's having to scan lots
of deleted records before finding the first real one, or are there other
possibilities? I must say that

Oldest transaction 390433636
Oldest active 390433637
Oldest snapshot 390433637
Next transaction 391092651

doesn't look too encouraging, so I'd better investigate that.

Tim Ward

Tim Ward