Leyne, Sean wrote:

> Martin,
>
>> Currently using 4096 and wondering if we should be using something
> else
>> to improve performance.
>
> Performance is only affected by database page size in exceptional
> situations.
>
> I would suggest that you review your application and the SELECT
> statements which it is executing. This is where I think would find the
> source of any performance problems.
>
> As to your question, there are 3 factors which I believe need to be
> considered in setting the page size value:

Also read Helen's book, it is mentioned in there.

>
> 1 - the Depth value from GStat (db statistics) for indexes. A value of
> 3 or more suggests that a larger page size is required.
>

This only works with data in the database, so does not help at the set
up stage. Firebird, however, does allow for changes in page size with a
backup and restore.

> 2 - the size of the data rows and/or the size of BLOBs to be stored. If
> you are going to be storing large BLOB; the bigger the page, the better,
> since this will reduce the number of fetchs the engine will need to
> perform.

Data rows, excluding blob type fields, should fit on one page (they have
to in SQLServer). Usually there will be many records on a page. Blobs
can be significant if loaded frequently, but ignore them if you only
load a blob field once a week.

>
> 3 - To a lesser extent, the size of the disk cluster (or is it sector
> size?) for the drive you are using. In an ideal world, I would format
> the drive so that the db page size and the cluster size would be the
> same.

OK if you have that much control and it does not adversely affect
anything else and you do not want to change the page size in future.

Having a db page size which is larger than a disk page could mean

> that the disk would need to perform multiple disk reads in order to read
> a single db page.

This will be the normal case for Firebird installs.

The growing prevalence of cache disk controller and

> SAN based disks, is reducing the significance of drive characteristics
> as factor to selection of database page size.
>

Yes, although users going for Firebird are likely to be the last in line.

regards
ian