Well, I tried setting up sth similar :
situation sketch :

I need to keep track of all dml on about 28 tables. (Those changes
need to be synchronized 'in realtime' with a copy database on another
server).
So for each of these tables I made a copy table with 'sync_' prefix
in tablename, which contains the same fields as the original + syncid
(pk), synctimestamp and updateflag (for I,U,D) and which is fed by
after triggers on the original tables.

I created a table SyncStats with TblId,Tblname,Todo, done as fields
with a record for each of these 28 tables.

I added after insert and after delete triggers on every SYNC_* table
to keep track of the count in the sync tables. (so that's twice 28
triggers (Insert and delete) working on this one table, each on its
own record though)

Every Insert in a Sync_* table results in a todo field increment,
a delete means a done field increment for the relevant 'synctable'
record.

All off this to give system admin a quick overview of the
synchronization status on all tables.

Although SyncStats table only contains 28 records, and all updates
are where tblid = xxx (so using Pk) it really slowed down the inserts
and deletes on the sync* tables !

So I had to let go off this mechanism, since my DBSyncronizer
application (running on master server) couldn't keep up with the
actual DML (it was running behind)

Maybe anybody got any other hints / ideas to implement this in a
different way ? I really don't need an absolutely accurate overview
of the count in the sync tables (so don't care about active
Transactions) , I only want to have an immediate overall overview to
see syncronization is ok and up to date with production data.

Sorry for this lengthly explanation, I made it as short as possible ;-
)

Thanks for any input,

best regards,
Dirk Naudts.


--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@i...> wrote:

> dirknaudts wrote:
> >
> >
> > It seems that doing a select count(*) from mytable, where mytable

has

> > a pk, FB 1.5.1 doesn't use that PK to speed up the count.
> >
> That's right. Using the primary key would in fact be slower than
> reading the table in many cases. Firebird tables are multi-

generational

> - a single record can exist in several versions and records that

have

> been deleted may not have been expunged from the table if they're

still

> valid for some active transaction.
>
> From the point of view of any single transaction a record may be

too

> new to be valid, valid, valid but the wrong version, or no longer

valid.

> The only way to screen them is to look at the actual records. If

you

> have to look at each record, the fastest method is to read the data
> pages in order.
>
> And no, it wouldn't be a great idea to put the transaction

information

> in the index to make select count (*) faster because it would

double or

> triple the size of indexes - increasing the size of each entry and
> increasing the number of entries.
>
> Some applications really need the number of records in large

volatile

> tables. One way to get that is with an external counter table.

The

> counter table consists of two fields, record_count and table_name.

Put

> before insert and before delete triggers on the tables to be

counted

> that store the table name and 1 for inserts or -1 for deletes.

From

> time to time, run a procedure that adds up all the counter records

for a

> table, deletes them, and stores the new total in the counter table.
>
> Regards,
>
>
> Ann