>> >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