Subject pretty much tells it all -- under what circumstances does
PreparedStatement use backend-based stored plans, and when are those
plans removed from the database?
I'm using JBoss which uses PreparedStatement for all of its queries,
and it uses its own datasource implementation. A typical page hit
involves:
1) connection yanked from the datasource, transaction started.
2) Various CMP finders / accessor SQL statements run using that
connection / transaction, but typically only one execution of a query
for each distinct query pattern. Inbetween each bean method the pooled
connection is closed, but I highly suspect that the same underlying
connection must be returned to the next bean in line enrolled in the
same transaction.
3) session bean method completes, transaction committed, connection
probably cleanly returned to the connection pool, but available to be
yanked 'next time'. Idle connections are aged out periodically and
finally closed.
From watching the statement logs on the backend-side, I don't believe
that I'm getting any backend stored plans in action (using PG 7.4.2 and
JDBC from CVS tip on devel boxes). Within a single transaction, the
odds of JBoss preparing the same statement more than once are low, but
across transactions, they are high, assuming the same session bean
methods called, so there may be some benefit if somehow, magically, the
same preparsed backend-plan from the previous run was chosen and used.
Or I may just be completely out of luck -- closing a PreparedStatement
if/when backend cached-plans (can't remember their real name -- grr)
are being used by the JDBC driver would then probably tell the backend
to cleanup said plan, eh?
I love O/R.
----
James Robinson
Socialserve.com