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