Thomas,

> - Prerequisites
>
> * Firebird 2.5 SuperClassic 64-bit on Win7 Prof.
> * Table with one primary key column CHAR(16) OCTETS
> * Inserting 100.000 records within an EXECUTE BLOCK and using GEN_UUID()
> * Data pages are pre-allocated, thus the database file won't grow during
> the test
> * In all tests, the fsql test session was the only one connected to the
> database
>
>
> - Result for SuperClassic
>
> I tested with various page buffer values and came to the result, that a
> higher page buffer value can improve execution time A LOT.
>
> Page Buffers    Execution time [s]
> =======================================================
> 75        70.24
> 256        7.41
> 512        2.26
> 1024        1.86
> 2048        1.68
> 4096        1.75
> 10000        1.75
> 50000        2.19
>
>
> As you can see, there is a huge step forward between a page buffers
> value of 75 and 256. When a database is newly created (as in your case),
> it pretty much uses the default one (e.g. 75 with SuperClassic). Thus,
> my initial slowness as reported in the list was affected by this. With a
> lower page cache one can also see, that CPU utilization is pretty low. I
> guess it's pretty much busy reading from disk. CPU utilization increases
> with a higher page cache like 256, 512.

Here's what happened. You've got an index with pretty big keys, which you
inserted in random order. The best situation is when the whole index fits
in cache, so you never have to reread a page. The second best is when
you can keep all of the higher levels in memory, so you only have to reread
the leaf levels which have a relatively low chance of being needed again
soon. The first improvement went from having to reread nearly every page
to keeping the hotter upper levels in cache, then keeping incrementally more
of the lowest level.

Cheers,

Ann