martinknappe wrote:

>
>
> Ok, I did that. When I change the page size from 4kbyte to 16 kbyte my
> queries execute *way faster. but what's all the fuss about? just to
> make myself some idea of how these pages are physically represented in
> the database and see whether there's a way to speed things up even more..
> is there a reason why ibexpert offers me page sizes of 4, 8 and 16
> kbyte or would it be equally meaningful to change the page size in the
> script to, say 3 kbyte or anything else?

Firebird supports only five database page sizes, 1K, 2K, 4K, 8K, and
16K. The two major performance issues are in indexes: depth and
time to scan a page.

The depth is the number of levels of indexes. Indexes are upside
down trees with the root at the top and the leaves at the bottom.
The root is a single page. The leaf level pages contain pairs of
key values and the corresponding record number. In a single level
index, the root is the leaf. In a multi-level index, the root
contains pairs of index key values and the page number of an lower
level page that starts with that key value. In a two level index
the root points to leaf page. In a three level index (the most
common for the page sizes and data volumes we deal with) the root
points to an intermediate level page structured just like the
root, pointing to leaf pages. Indexes with more than three levels
hurt performance. Use gstat to get the index statistics. If you
have indexes that are more than three levels, increase the page
size.


The second issue is the time to scan an index page. Indexes keys
are compressed and variable length. If you're looking for the key
value "abcdef" and the page starts with "abaaab", you must look at
each key until you find "abcdef" or a higher value. That's not
much work, but it is some and in larger page sizes the cost of
scanning becomes significant. V2 addresses that problem with an
internal index to the index page. That will make 16K pages more
attractive.


Regards,


Ann