paulruizendaal wrote:

> 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.
>
>

Paul, I'm afraid "hurd" isn't quite English. You can chose between herd
and heard, as in:

We're off like a herd of turtles!
A herd of turtles?
Of course I've heard of turtles!
Why would I care what a turtle heard? I have no secrets from turtles...

That said, I agree with most of what you said about caching compiled
statements. There are three reasons that MySQL doesn't support a
compiled statement cache. First, they don't have a runtime structure --
just an near infinite number of flags that nobody particularly
understands. It's very hard to capture something with the consistency
of a jellyfish. Second, their handling of parameters has been so poor
that nobody uses them. They were originally handled client side, which
was horrible, but the server implementation was so buggy as to be
unusable. They tell me that often work now, but everyone is afraid of
them. MySQL users invariably construct SQL commands with string
concatenation, making SQL injection an international sport. I have the
misfortune of living on a street with a name containing an apostrophe.
Generally speaking, if I enter the apostrophe, I can't order from Web
sites that use MySQL. The third reason is that result caching works
much better than statement caching on most competitive benchmarks, even
if they are next to useless in practice.

The more modern practice is to use memcached so at least the stale data
is on your local machine. It doesn't always get the right answer, of
course, but then neither does result caching.

Result caching is a disaster with MVCC and transactions. Falcon just
turns it off.

--
Jim Starkey
President, NimbusDB, Inc.
978 526-1376