Hi David,

The problem with events is that someone has to be around to monitor
them. I guess an application could do a full count and then monitor
events for the duration of the session. Without knowing the full
details of how you need to use this information it is difficult to say
whether this would work as desired or not.

There is a scenario that I have contemplated before, but never
actually implemented. It goes something like this...

The create a table that holds a single integer value. Attach an insert
trigger to the main table that inserts its a record carrying 1 in the
integer field of this alternative table and a delete trigger that
inserts a record carrying -1 in the integer field.

You then need a batch mechanism that will delete all records from the
alternative table and then calculate and store the full record count
from the main table. (Make sure that only one copy of the batch
mechanism can run at a time - presumably by locking the full record
count storage record - whereever you decide to keep it.)

Now you can create a count process that reads the full record count
(from where ever you stored it) and then does a SUM(integer_field) on
the alternative table to adjust the full count accordingly. To be
completely safe the count process should first see if the batch
process is currently running - and if so either wait for it to finish
or do a full count against the main table.

Because the batch mechanism should keep the number of records in the
alternative/counter table to a minimum, the SUM() should take place
very quickly and be more efficient that a COUNT() over the entire main
table.

It will obviously work best if you have an always running service
application that can perform the batch process when there are the
minimum number of users. Other possibilities exist (such as
executing at application startup) but are more likely to hit update
conflict problems.


At least that was my theory ;-) I dont know if it works well in
practise because the need to try it went away before I got around to
experimenting. Much probably depends on the nature of the main table,
if you are inserting and deleting thousands of records every hour then
this idea is probably not appropriate.

FWIW

--
Geoff Worboys
Telesis Computing