Subject Re: [firebird-support] set page buffer on client connection not work
Author Ann W. Harrison
Ivan Prenosil wrote:
> In classic server you should be able to increase number of buffer pages
> for single connection only.
> But playing with cache size will not solve your speed problems anyway.

Actually, I think it may help in this case.

He's already got data showing that a transaction that inserts 40 records
and deletes another 40 takes 9,252ms with 75 pages of cache and 156ms
with 2048 pages.

svanderclock is running classic on a database with 40 indexes per table.
Given his data - lots of records with very similar values and arbitrary
queries on it, all limiting the results to the first 200 or so in a
range - the design is defensible. His normal read transactions will
reference a few tables and a few indexes per table, so they don't need
a lot of cache. His inserts, on the other hand, change 40 indexes, so
they are cache hogs.

The solution for this application may be to set the database cache size
to 0 and let read attachments pick one cache size and inserts another.