Re: Under what circumstances does PreparedStatement use stored plans?

On Apr 9, 2004, at 7:04 PM, Oliver Jowett wrote:
>
> In the CVS driver, it's controlled by calling a method on
> org.postgresql.PGStatement on the prepared statement in question; by
> default it's off.
>
> For a patch that provides a bit more transparency try:
>
> http://archives.postgresql.org/pgsql-jdbc/2003-12/msg00019.php
>
> This patch allows you to specify a threshold at the datasource level
> (via a property or URL parameter). PreparedStatement objects that are
> reused more than the threshold begin to use server-side prepared
> queries automatically. I put this patch together for a similar case to
> your JBoss case -- where server-side prepared queries are useful, but
> modifying the actual JDBC client code to call a postgresql-specific
> method isn't easy.
>

That looks rather useful, but not as an island in and of itself (for
tricking out JBoss, anway). In JBoss's case, the PreparedStatement will
most likely get closed before an equivalent one gets created / used, so
some sort of longer-lived threshold would have to take control -- at
the connection level. Also at the connection level would be the set of
currently prepared queries, so that when JBoss calls perpareQuery(), we
could:

1) Get the hashcode of the query to see if we've seen anything like it
before. Then use it to check a map of hashcode -> {threshold, prepared
query handle} (would hate to directly map the query string itself --
kiss my RAM goodbye -- but would have to stash the query string itself
for the the prepared queries to guarantee we're about to use the
correct plan).

2) If the map's value does not have a query handle yet, increment the
hitcount on it and test against the threshold. If we pass the
threshold, then prepare query server-side and retain handle.

3) If the map's value does have a query handle already (and the query
strings match exactly), then we're already planned, so use it.

So, ultimately, when JBoss checks out a connection from the datasource,
that connection may well already have a server-side prepared plan for
the query it is about to make, and/or increase the chances that this
query will be prepared in the future.

This sort of system would violate the contract of Statement.close(),
which should free up any resources, client or server side, associated
with this statement. But without it, I can't see how prepared
statements could ever really be used effectively in a pooled datasource
scenario outside of the occasional method that knows it is going to
fire off the same query many times in a loop. What I'd like to see use
prepared queries would be (at least) our finder methods that do many
joins -- a place where the planning cost might well be non-negligable.

Anyone know off the top of their heads any particular resource limits
server-prepared queries cost? Should any attempt at this scheme take
into account any particular upper-limit of prepared queries?

Perhaps something along the line of the N most-frequently used queries
should be prepared, once the use count crosses a threshold.
Ugh. One wonders if the optimization would be worth the effort involved
in coding as well as the runtime costs of tracking which queries are
parsed or not.

That threshold hack to prepared statement does indeed look crafty for
some cases though, just not mine, which, admittedly, is probably not
the general case. Is it considered for inclusion in the mainline
driver?

----
James Robinson
Socialserve.com