Re: CVS JDBC driver will try to use server-side-prepare on unpreparable SQL

On Fri, Aug 15, 2003 at 09:55:02AM -0700, Barry Lind wrote:

Hi Barry,

Thanks for the detailed response!

> Yes and no.  The plan is to convert fully over to the new V3 protocol
> which will better handle cases like this and a lot of other things.  So
> yes the plan is to move fully to server side prepared statements, but
> via a different mechanism.  And conversly the plan isn't to move the
> current mechanism forward as it has many limitations (as you are finding
> out).  One of the big reasons for the new functionality in the V3
> protocol is to provide better support for these type of opperations
> efficiently.
>
> However a workaround for this specific problem would be to only use
> server side prepared statements in the current implementation for
> executeQuery calls, not for executeUpdate or for plain execute.

Unfortunately it's executeUpdate() where I benefit from server-side prepare..

> >Should we only be doing PREPARE on queries that are known to be safe (e.g.
> >single-statement SELECTs), or is it better to try to catch the errors and
> >abandon the prepare? (more general, but sounds a bit hairy).
> >
> >The reason that this came up is I'm modifying the driver to allow
> >server-side prepare to be toggled at the connection- and datasource- level.
> >Patches for that to follow once I've sorted this problem out.
> >
>
> I would rather see you invest your time in implementing the V3 protocol
> to do this correctly.  I am reluctant to commit patches along the lines
> of what you are describing (check the archives for previous discussions
> on this).

I had a search in the archives .. nothing conclusive. There was:

 http://archives.postgresql.org/pgsql-jdbc/2003-01/msg00012.php

(and replies) where the main objection seemed to be to turning on
server-side prepare *by default*. Did you have another thread in mind?

I'm not sure if we're ready to move our system to 7.4 & V3 at this stage.
The JDBC driver seems somewhat less mature when it comes to the V3 protocol,
and at the moment it seems like the additional development time needed to
clean up the driver for V3 outweighs the benefits we'd get (for our app,
anyway). I'm happy to be convinced otherwise, though.

> But in short the reason is, that in general using the current
> prepared implementation will be *slower* than not using it, unless you
> are reusing the statement a number of times.  Therefore unless you have
> some sort of complex application layer that is caching Statement objects
> and reusing them, this feature will nagatively impact performance, and
> IMHO will lead to problems because people will assume that something
> like this should be used and complain when it makes things slower.
> Since in order to be useful you need application logic to cache and
> reuse the Statement objects, it isn't that difficult to have that logic
> also turn on server side prepare using the current methods.

My situation is that I *do* have an application that caches
PreparedStatements that are frequently used; however, there is no general
framework for this in place. It's a decent amount of work to get an
efficient framework from this in place as you need to key the statement
cache on both connection and query for the general case. If you're not
writing a general framework you can take advantage of the app structure. In
this case, I know that a particular object exclusively owns a single
connection, and I know the set of statements that the object will execute ..
so I can just prepare statements aheadof time and store them directly on the
object itself.

To turn on use of server-side prepare with the current driver, I must cast
every PreparedStatement I create to org.postgresql.PGStatement and call
setUseServerPrepare() (either explicitly or via a helper). Currently, our
app code is postgresql-independent and quite simple. Ideally I'd like to
keep it that way!

I looked for the "obvious" solution of being able to change the default
use-server-prepare at a connection or (even better) datasource level. If I
can do it at the datasource level, this means there is *no*
postgresql-driver-specific code involved (we initialize datasource objects
from a config file via reflection) assuming I can sort out this CREATE TABLE
problem in a driver-independent way (e.g. fix the driver!). But there's
nothing there to do that currently. Yes, you can do this in other ways, but
it's going to be considerably more complex .. for this app, it might double
the code needed.

I understand that in many cases turning on server-side prepare everywhere
will actually slow things down. In this case, though, it'd speed things up
.. but the driver doesn't let me turn in on globally even though I know what
I'm doing. So I'm scratching an itch :)

> Finally, if you do want to pursue your current course, I would like to
> see some sort of benchmarks that show these changes actually on average
> help.

On average they won't help, that's why you wouldn't turn it on
indiscriminately, in the same way you don't use
PGStatement.setUseServerPrepare() indiscriminately.

The app in our case is essentially a big insert engine. Using server-side
prepare seems to give us a 15% or so improvement: (these stats are a bit old
and not very rigorous -- the postmaster is competing with the java process
for CPU -- but you get the idea)

Without server-side prepare:

 ===== testBigDB/persisted=====
  Testing with 10000x10 200-byte creates
 [...]
 Insert time: 14603ms
 Flush time:  76991ms

With server-side prepare:

 Insert time: 14312ms
 Flush time:  62835ms

(the total SQL time is insert + flush; "insert" in this case means "insert
in-memory"; the SQL bit is a write-back cache, "flush" waits for outstanding
SQL work to end)

-O