Jim & others,

I think we all agree that most execution time of 'database
procedures' will be spent in running the relational code, not the
procedural/OO glue. Perhaps we also all agree that 3-tier apps tend
to generate a lot of boilerplate queries that return the same result
set all the time (for instance, the appserver layer of Compiere
issues about 20 queries to build the data driven layout of the order
entry screen -- and users infrequently change the layout of their
screens).

It would be good to use some caching. The first optimisation is to
use prepared statements, a second to cache the compilation of a query
with its source text as cache key. Jim is proposing the latter for
Nimbus, it is known as 'cursor sharing' in Oracle and as 'query
caching' in MySQL. Oracle's implementation is quite clever in that it
can automatically turn obvious constants into parameters.

A next step is caching the result set. This can happen on the server,
as in Oracle 11gR1 or on the client as in SQLServer 2005+. In both
cases, the result set is invalidated when *any* change to a source
table happens. Crude, but correct. In the SQLServer case, it uses a
mechanism somewhat similar to Firebird events to notify the relevant
clients to update their cache (sounds to me like such implementation
has a thundering hurd problem, but perhaps I'm mistaken -- it cuts
down on the round trips though).

A more clever caching & invalidation strategy might be to:

[1] Bundle related queries (eg. the 20 mentioned above) into a
procedure and cache that (both compiled code & result). Make it a
property of the procedure whether it is cacheable or not;

[2] Keep track of all tables & pages touched by the procedure. For
mod's to a dependent table, compare the list of modified pages to the
touched list and only invalidate if there is a match;

[3] Cache the result set on both the server and the client. Use
something like the SQLServer scheme to invalidate clients. Use the
server cache to deal with the thundering hurd.

Comments, suggestions anyone?

Thanks,

Paul