Re: Under what circumstances does PreparedStatement use stored plans?

>
> There's no hard upper limit. The cost is basically the backend memory
> space needed to store the query parse and plan trees, which of course
> depends quite a lot on the complexity of the query, but I'd think we'd
> be talking a few kilobytes for typical queries. So you could probably
> store order-of-100 prepared plans without thinking about it, even in a
> system with a lot of active backends.
>
> regards, tom lane
>

Heck then, it would be much easier then to plan 'em all, retain plans at
the driver scope, and when an equivalent prepared statement gets
prepared, automagically connect it up with the already-prepared
server handle. JBoss (in our observation, anyway), ages out old
connections, so these resources should ultimately get released.

The Map of unique query identifiers -> prepared plan handle would have
to be synchronized, as well as what the unique query identifier actually
would be would have to be thought out. Mapping the query pattern String
itself would in most cases be a vast memory leak, but would guarantee
uniqueness. The hashcode of the string doesn't claim to be 100% unique,
(although it seems to try hard to target unique numbers), so it'd be
out as
well. MD5'ing the string and saving the resulting byte array into a
nicely
hashable object might then be the way to go. I suppose this all assumes
that the lookup + maintenance of such a datastructure would ultimately
cost less than re-planning all queries all the time. If my database box
CPU
is lower than my appserver box's CPU, then I'm designing an optimization
which lacks a problem, aren't I? Or I'm bored with business-logic code.

----
James Robinson
Socialserve.com