Re: Caching of Queries

> I think it's very hard to cache results on the client side
> without guidance because it is expensive to notify the client
> of change events. A changing table couldn't be cached on
> client side without a synchronous check to the db - defeating
> the purpose.

This is very true. Client side caching is an enormous win for apps, but it
requires quite a lot of logic, triggers to update last-modified fields on
relevant tables, etc etc. Moving some of this logic to the DB would perhaps
not usually be quite as efficient as a bespoke client caching solution, but
it will above all be a lot easier for the application developer!

The other reason why it is god for the DB to support this feature is that in
typical web apps there are multiple web/app servers in a farm, but mostly
just one live DB instance, so effective client side caching requires a
distributed cache, or a SQL proxy, both of which are the kind of middleware
that tends to give cautious people cause to fret.

As a side effect, this would also satisfy the common gotcha of count(),
max() and other aggregates always needing a scan. There are _so_ many
occasions where 'select count(*) from bar' really does not need to be that
accurate.

So yeah, here's another vote for this feature. It doesn't even need to
happen automagically to be honest, so long as it's really simple for the
client to turn on (preferably per-statement or per-table).

Actually, that gives me an implementation idea. How about cacheable views?
So you might do:

CREATE [ CACHEABLE ] VIEW view
[ MAXSTALEDATA seconds ]
[ MAXSTALEPLAN seconds ]
AS ...

That would be tidy I think...

M