Re: cacheing metadata

Dave Cramer wrote:
> There is some interest in caching meta-data. I am wondering what the
> community thinks of the idea.

Are there really metadata-query-heavy apps out there that would benefit?
  Do you have an example of one? I'd have thought that most if not all
of the metadata-related tables would all be sitting in cache anyway.

It seems like this is a special case of the more general "I run
expensive queries and don't want to rerun them to pick up changes". And
a very special case at that -- metadata doesn't usually change very often.

If the app is essentially a UI frontend I'd say you could get away with
caching results in the app (not the driver) and having a "reload" button.

If the app really needs up-to-the-moment metadata state to control
whatever actions that follow, it'll need to hit the DB with the full
metadata query while in a transaction to get the right isolation
characteristics, won't it?

> Notionally it would be used to speed up DataBaseMetaData queries. It
> will require some work on the backend to create triggers on schema
> changes. This is being considered as we speak.

If you're going to be adding support triggers to every DB anyway, why
not bite the bullet and do a stored proc that maintains a cache table of
materialized metadata, or something similar?

-O