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