> In this scenario, ANY update to the table will cause all the clients
> to requery the Db. This is what I am trying to avoid.

First things first,

The same thing happens when everyone switches on their computers at
9am and all connect and get the list within a short interval. Unless
you are storing data locally at each client and synchronising, you
are probably doing a brute force query to fill your lookups.

The first thing you need to decide is how quickly the clients react
to the post event. Do they need to all jump to life the millisecond
they get the request, or is 30 seconds later acceptable??

Assuming that 30 seconds later is acceptable, when the client
receives the event message, they wait a random interval U(0..30) and
then make the request. 30 seconds is just a number I pulled out of
the air. You should always consider the complexity of the query, the
urgency of the data change, the hardware capabilities, and the
network infrastructure. Perhaps it is more like 5 seconds, perhaps 10
minutes.

I also suggested you may want to add an index to this timestamp
column, so that even though each of the clients would need to
execute, most of them would not have to return any records. Each of
the "has anything changed" queries would be over in a few
milliseconds at worst.

> Imagine the Table has thousands of records, but each client is
> watching only a few - 3 or 4.

That doesn't scare me a bit.

The original query might look something like this

select ID, Name, LastChanged
from MyLookupTable
where ID in (10045,35540,69900,80043)

Of course it uses the PK index so only 4 records need to be hit and
the thing returns very quickly.

I then note that the largest LastChanged was '7/18/2005 9:00'.
Someone then updates ID 20000, and your client receives the event
notification.

3 seconds later (that was random), your client issues the following
query.

select ID, Name, LastChanged
from MyLookupTable
where ID in (10045,35540,69900,80043)
and LastChanged > '7/18/2005 9:00'

If you are only watching 4 records, then an index on LastChanged is
probably not going to give a noticable improvement in performance.

0 records are returned, and your app carries on.

You could ignore any notifications for the next 30 seconds or so
because your data is probably "recent enough".

If the number of clients are Large, a more important consideration is
the firewall rules that may be in place preventing the clients from
receiving an event.

In our system, we have a table with a unique record for each client.
If a change occurs in an "interesting" table, triggers automatically
work out which clients need to know and insert a record in a
notification table. Periodically, the clients check the notification
table and remove the records they now know about. Again it is a
question of urgency. For us, although instantaneous notification is
desirable, 5 minutes turned out to be the ideal tradeoff between
chattyness and instant notification.

Adam