>> >Get the statistics on the various runs of the sub-procedure- reads,
>> writes, fetches, and marks.
>> No writes. 47 000 indexed reads.
>Lets try that one again. I'd like both reads and fetches, with statistics
>for a run of the subquery that's fast and one that's slow. Did you mean
>that computing one account balance involved forty-seven thousand indexed
>reads? That's some account!
Yup. BUT, the weird thing is, it's that SAME account that is sometimes fast and
sometimes slow. It just depends on what other table/account you've queried
beforehand.
>Gstat will tell you how deep your indexes are. Firebird indexes are very
>broad based triangles, with a single page at the top, lots of pages on the
>>next level down, and enormous numbers of pages on the level below that.
>The taller (or deeper) the triangle, the slower it is. The height and width
>are determined by page size and key size. An index with four or more >levels
>is an indication that you should increase the page size for your database.
>Run gstat and search for the indexes used by this sub-procedure.
Max depth in the whole database is 3 ;-)
>I'm sure you mentioned it somewhere, but what is the database page size?
> And the cache size?
Generation 1311
Page size 8192
ODS version 11.2
Oldest transaction 1292
Oldest active 1293
Oldest snapshot 1293
Next transaction 1294
Bumped transaction 1
Sequence number 0
Next attachment ID 9
Implementation ID 16
Shadow count 0
Page buffers 1000 <<<< was 150, but I tried increasing to
see if it would make a difference - it didn't seem to have any effect
Next header page 0
Database dialect 3
Creation date Sep 11, 2012 11:43:22
Attributes force write, no reserve
Sweep interval: 200000
