Re: Under what circumstances does PreparedStatement use stored plans?

[ forgot to cc list ]

On Apr 13, 2004, at 2:42 PM, Tom Lane wrote:

> But doesn't the JDBC
> API distinguish prepared statements from unprepared ones? ISTM it is
> the app programmer's responsibility to prepare just those statements
> he's likely to use more than once.

The core JDBC API does not (AFAIKT). The closest difference is using
PreparedStatement as opposed to vanilla Statement, where the docs
for prepared statement reads:

An object that represents a precompiled SQL statement.

A SQL statement is precompiled and stored in a PreparedStatement
object. This object can then be used to efficiently execute this
statement multiple times.

Which seems to imply "backend prepare-me". Currently, Postgresql's
PreparedStatement doesn't, well, prepare, unless you downcast to
the implementation class and call a specific method. This may or may
not be planned to go away in the future, I do not know.

The "trouble" I'm in is one of middleware. JBoss runs all of its queries
through PreparedStatements on behalf of the lowly app-logic code
relatively unbeknownst to it. Some methods map out to SQL queries
every time, others sometimes, and others none, given the myriad of
config + application deployment options, the state of the JBoss object
cache, and the phase of the moon. But a common class of queries
produced by JBoss (and any EJB container) are known as 'finders',
and they typically involve joins, and they cannot be optimized away.
Typically, each finder invocation happens with a 'fresh' DB connection
yanked from the datasource pool of connections, and a new
PreparedStatement is created each invocation. If this were not
middleware-hosted, then your comment is dead-on, and is what the
JDBC specification writers implemented. I'm exploring a system-wide
optimization even-with our poisonous middleware piece.

Sometimes I envy you folks who get to live in the good old days
of "C" and GDB.

----
James Robinson
Socialserve.com