Обсуждение: Re: Under what circumstances does PreparedStatement use stored

Michael Nonemacher wrote:
> That certainly may be true, but I prefer the earlier point of view that
> someone using PreparedStatements for portability and not performance
> shouldn't be surprised when his database interaction isn't as fast as it
> could be.

Well, sure, but if we can avoid a performance penalty for this very
common case at essentially no cost, why not do so? We really don't want
to be encouraging people to insert their parameters into their queries
by hand!

> Also, how does this play with batching?  It's possible (and even
> encouraged) to have JDBC code that looks like this:
>   ps = c.prepareStatement("update my_table set name=? where id=?");
>   for (Iterator it = list.iterator(); it.next(); ) {
>     Thing t = it.next();
>     ps.setString(1, t.getName());
>     ps.setInt(2, t.getId());
>     ps.addBatch();
>   }
>   ps.executeBatch();
>
> (Code that ensures the batch remains at a reasonable size left out for
> clarity.)
>
> I think you'd want to consider statement-batching when considering
> prepared statements.

The driver is currently very dumb when it comes to statement batching;
the above code won't actually give you any performance benefit over
running the statements individually, as the implementation of
executeBatch() just executes one query per set of parameters
synchronously. So (with my original patch) you'd still get the benefit
of PREPARE/EXECUTE after the first N items are updated, but it's not
going to be as fast as you expect regardless..

But even with a smarter implementation it seems simple enough: count
each addBatch() towards the threshold and check the threshold on
executeBatch().

-O

On Apr 13, 2004, at 6:36 PM, Oliver Jowett wrote:

> So (with my original patch) you'd still get the benefit of
> PREPARE/EXECUTE after the first N items are updated, but it's not
> going to be as fast as you expect regardless..
>
> But even with a smarter implementation it seems simple enough: count
> each addBatch() towards the threshold and check the threshold on
> executeBatch().

It sounds to me like Oliver's original patch would solve most all
'normal' cases reasonably well, including the case when people would
want all PreparedStatements to be server-side prepared via setting the
threshold to 1. It would not solve the 'fight-the-middleware
cross-PreparedStatement pooling' scenario I face, but it sounds like a
little-to-loose patch -- backwards compatibility is maintained, and you
can get server-preparation without downcasting if so desired, either
always or past a static barrier.

Is it a candidate for commitment?

----
James Robinson
Socialserve.com

On Tue, 13 Apr 2004, James Robinson wrote:

>
> On Apr 13, 2004, at 6:36 PM, Oliver Jowett wrote:
>
> > So (with my original patch) you'd still get the benefit of
> > PREPARE/EXECUTE after the first N items are updated, but it's not
> > going to be as fast as you expect regardless..
> >
> > But even with a smarter implementation it seems simple enough: count
> > each addBatch() towards the threshold and check the threshold on
> > executeBatch().
>
> It sounds to me like Oliver's original patch would solve most all
> 'normal' cases reasonably well, including the case when people would
> want all PreparedStatements to be server-side prepared via setting the
> threshold to 1. It would not solve the 'fight-the-middleware
> cross-PreparedStatement pooling' scenario I face, but it sounds like a
> little-to-loose patch -- backwards compatibility is maintained, and you
> can get server-preparation without downcasting if so desired, either
> always or past a static barrier.
>
> Is it a candidate for commitment?
>

I've reviewed it and it basically looks good, but I came across a general
issue with server prepared statements not specific to Oliver's patch.
Essentially when doing "int count = prepStmt.executeUpdate()" the count is
never set for server prepared statements.  I've raised this issue on
hackers: http://archives.postgresql.org/pgsql-hackers/2004-03/msg00923.php

Basically I want to run the whole regression tests with everything using
server prepared statements cleanly.  Sure the patch doesn't affect the
long standing behavior, but it makes in much more accessible and I'd
expect a number of bug reports.  There were a number of other failures as
well, but I don't recall what they are at the moment.

Kris Jurka

First off, many thanks for everyone's time.

Quick answer: JBoss can already do it, but we'll need our server
prepared statements to be able to report the number of rows updated by
an UPDATE command (Just as Kris reported was needed of 7.5.). It checks
the result, and really wants to see the number it expects, at least
when it thinks it knows the answer:

    org.jboss.tm.JBossRollbackException: Unable to commit,
tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=jlrobins.local//29,
BranchQual=]     status=STATUS_NO_TRANSACTION; - nested throwable:
(javax.ejb.EJBException: Update failed. Expected one affected row:
    rowsAffected=0id=44596); - nested throwable:
(org.jboss.tm.JBossRollbackException: Unable to commit,
tx=TransactionImpl:XidImpl     [FormatId=257, GlobalId=jlrobins.local//29,
BranchQual=] status=STATUS_NO_TRANSACTION; - nested throwable:
(javax.ejb.EJBException:     Update failed. Expected one affected row:
rowsAffected=0id=44596))


Long Answer:

After more digging around in JBoss, first verifying if indeed JBoss
uses its own PooledConnection implementation or uses the JDBC driver's
implementation, I found that it uses its own, so caching prepared
statements in our own JDBC driver's implementation would have done me
no good. So I dug into the code in JBoss 3.2 cvs head corresponding to
the classes that it reported implemented the DataSource and the
Connections returned by the DataSource
(org.jboss.resource.adapter.jdbc.WrapperDataSource and
org.jboss.resource.adapter.jdbc.WrappedConnection, found in cvs module
jboss-3.2, subdir connector/src/main/org/jboss/resource/adapter/jdbc)
and poked around.

Their WrappedConnection made reference to a Map of prepared connections
(if it had been constructed or subsequently tweaked with a prepared
statement pool size, replaced LRU, with the key being the SQL template
passed into the Connection.prepareStatement(String sql) method.

Then the trick was to figure out how to enable this JBoss feature,
configuring the beast being a twisty maze of XML rooms, each one
looking the same as the rest. Their search engine finally turned up a
tag "<prepared-statement-cache-size>" which can be embedded in your
datasource config XML file (see your jboss distro,
/docs/dtd/jboss-ds_1_0.dtd). This enabled this feature in our copy of
JBoss 3.2.3 (but didn't make 3.2.1 flinch at all -- bug? Unimplemented
at that time?).

The only way I could enable server-side statement preparation to really
test this out was to then inject a hack into our
AbstractJdbc1Statement's AbstractJdbc1Statement (BaseConnection
connection, String p_sql) constructor, making an explicit call to
setUseServerPrepare(true) so as to force server preparation for all
PreparedStatements. And then things went well, up until the point at
which it tried to do an update, at which the lack of being able to
return the count of updated rows made the beast upset.

But up until the first update statement, I watched transactions start,
queries get prepared, then executed, then not deallocated, since JBoss
was keeping the PreparedStatement open. Life with backend 7.5 will be
good indeed!

I suppose I could further refine my driver hack to only force-prepare
if it smells like a SELECT as opposed to UPDATE / INSERT for further
experimentation to see if it ultimately is worth it.

----
James Robinson
Socialserve.com

Kris Jurka wrote:
> Basically I want to run the whole regression tests with everything using
> server prepared statements cleanly.  Sure the patch doesn't affect the
> long standing behavior, but it makes in much more accessible and I'd
> expect a number of bug reports.  There were a number of other failures as
> well, but I don't recall what they are at the moment.

I just tried changing the driver to only use server-side-prepare for
SELECT statements, and changed the default value of useServerPrepare to
true so all statements are candidates for preparation. With those
changes, we pass all of the regression tests except for the
ResultSetMetaData tests. I haven't dug into the cause of the failures,
but it looks like the metadata has no column names for some reason.

Assuming the RSMD failures can be fixed, perhaps a change along those
lines when talking to a <= 7.4 server would let us turn on more
aggressive use of PREPARE?

-O