On Fri, Dec 13, 2013 at 4:15 AM, Tim Ward <tdw@...> wrote:

For x records it's usual to see 2x fetches. If this creeps up to 3x fetches and your application slows down by several orders of magnitude it may be that you have fragmented records, with each record split across two or more pages.

The reason for 2x fetches is that Firebird identifies a record through a slot in a vector on the data page - both the page and the slot are part of the RDB$DB_KEY.   The page is identified indirectly through an index of pages for the table.  The values in the vector on the data page are the length of the stored record and the offset on the page of the first byte of the record.  So once a data page is in memory, Firebird reads the records on it first by looking p the offset and length, then by reading that length from that offset.  Even for a natural scan, Firebird has some overhead fetches - first to find the right page in the index of data pages for the table, then to access that page to read page numbers.  Indexed access requires more fetches...

Fragmented records increase the number of fetches - and possibly the much more expensive physical reads -  as do old record versions.

Good luck,

Ann