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

Having "no reserve" isn't a good option for a regular read/write
production database, because this basically prevents having back record
versions on the same page as the primary record version, thus additional
page reads are necessary.


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/