Subject RE: [firebird-support] Any workarounds for performance issues with multi-generational architecture.
Author Nigel Weeks
> 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.

That's right.

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

Not just Firebird, but all databases are slow on brute-force scans/counts
without cached row counts.

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

Heck yeah!

Ann Harrison posted a ripper a few months back, and it works a treat!
In a nutshell (and slightly modified)

Create a table, with a schema like such:
CREATE TABLE tbl_dbrows (
str_tablename CHAR(31) NOT NULL,
int_count INTEGER,
PRIMARY KEY(str_tablename)

Add triggers to each table to do the following
For each insert, insert a record into this table, with a '1' in int_count.
For each delete, INSERT a record into this table, with a '-1' in int_count.

Notice the lack of updates and deletes - far slower operations!

Periodically, sum the entries in this table, remove the counts, and store
the summary.
`select sum(int_count) from tbl_dbrows where str_tablename = 'MyTable';`
`delete from tbl_dbrows where str_tablename = 'MyTable';`
`insert into tbl_dbrows (str_tablename,int_count) values
('MyTable',2238762);` (or whatever your summary count was)

Then, the magic part is, to find out how many records exist in any table,
simply do a sum on int_count for the table you want! Lightning fast!
Triggers will always fire, so it'll stay in sync. Beautiful!

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

Nope. Asking it to run like a dog will result in exactly what you asked for.
> Does Firebird 2.0 improve the performance of this scenario in any
> way?

Don't imagine so.