Subject Any workarounds for performance issues with multi-generational architecture.
Author bigloudjeff
I hae just spent a couple of weeks writing a pseudo-DAO wrapper for
firebird so that I could more easily migrate a legacy code base to
firebird to overcome some JET database limitations. I finished the
initial version and started doing some testing the results on some
smaller/slower hardware and found some disappointing results.

In order to emulate DAO's recordset functionallity, I have to do
things like select * from table or select count(*) from table. The
tables I am dealing with have ~100,000 rows. On my developemnt
machine I did not notice any big performance hit. However, it seems
that on a laptop with limited ram and a slower drive the performance
penalty of these types of statements is quite big. Executing "select
count(*) from table" takes about 6 minutes on the laptop I am
testing on.

After doing some research into this, it seems that Firebird's multi-
generational architecture does not lend itself well to operations on
large result sets in limited resource situations. My understanding
is that no matter how the select is performed (with or without an
index) all of the records that could end up in the result set have
to be visited directly to determine which one/version should be
included in the count or the result set.

Unfortunately, I am not able to make major changes to the legacy app
I am trying to convert. So, I need to be able do things like finding
out the number of rows in a table/recordset before fetching them all.

Are there any know workarounds for these scenarios?

Are there any configureation paramters other than cache and page
size that effect this behavior?

Does Firebird 2.0 improve the performance of this scenario in any

Any help in this would be greatly appreciated.