At 12:20 AM 13/12/2005 +0000, you wrote:

>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.

False. It's not MGA that is getting in your way...

>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

Yes, for count(*) without a limiting WHERE clause

> or the result set.

No. Quite wrong.

>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.

If you have to either count all the rows, or fetch them all, then you
shouldn't be considering moving to a DBMS that is optimised by design for
concurrent use by multiple users on a network. These are legacy features
that were needed by a legacy desktop database engine and have no use in a
truly multi-user setting.

>Are there any know workarounds for these scenarios?

Learn SQL ? Understand the virtues of the WHERE clause and the kinds of
indexing that work for and against retrieval performance.

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

Cache and page size don't affect "this behaviour". Appropriate indexing
certainly does improve performance (though it doesn't and never will affect
the performance of select count(*) in an unlimited select) but your legacy
indexes almost certainly will grind it to a halt. (Desktop DBMSs have
quite different uses for indexes, as a general rule.)

>Does Firebird 2.0 improve the performance of this scenario in any
>way?

Firebird 2.0 does improve performance of some indexed searches quite
dramatically. But improved indexed searching won't solve your problem if
either (or both) your indexes are inappropriate for the operations you are
doing (joins, sorts and searches) or if you are not doing operations that
use indexes.

Your problem is your requirement to stick with techniques and structures
that were designed for file-served data that is read from (and written to)
the hard disk directly by one person at a time...the "free beer" version of
MySQL is more akin to that, if you have enough machine resources to run MySQL.

./heLen