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 n...@telensa.com [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 t...@telensa.com
<mailto:t...@telensa.com> [firebird-support]
<firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com>> wrote:
SELECT FIRST 1 <several columns> FROM MYTAB ORDER BY MYKEY1, MYKEY2;
PLAN (MYTAB ORDER PK_MYTAB)
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
