Re: [GENERAL] 7.3 Prepared statements

Barry:

Thanks for the feedback.  This helps alot.  Feedback/questions below.

Barry Lind wrote:

> Charles,
>
> You can have the jdbc driver use stored plans, but that is not the
> default behavior.  There is a method on the org.postgresql.PGStatement
> object to enable using a stored plans.
>
> It is not the default behavior in 7.3 for the following reasons:
>
> 1) The functionality on the server is new and therefore there are
> probably a few bugs yet to be found.  I didn't want to destabilize the
> overall jdbc driver by relying on new functionality.
>
> 2) The implementation on the server will not allow all current uses of
> JDBC PreparedStatements to work (i.e. prepared statements that do
> multiple sql statements in one call, ususally done to reduce number of
> network roundtrips when needing to issue many sql calls at the same
> time).

Do you mean that the driver, in this mode, will disallow various forms
of JDBC prepared statements, or that the stored plan support will not be
supported if the query is of one of these forms?  I hope it is the
latter, as that would make porting to the new driver easier and avoid
the re-port to a new driver issue.  We use PreparedStatements *for
everything* and it is part of the framework that we use to do it this
way.  I think it would make the new driver unusable to us if we could
not use the PreparedStatements for certain, currently legal, SQL.

>
> 3) The current implementation on the server will cause a decrease in
> performance if you only use the PreparedStatement once.  Since the
> implementation of server side prepared statements requires multiple
> roundtrips to prepare, execute and close, there will be more network
> traffic and thus decreased performance if the JDBC prepared statement
> is only used once.  And my experience is that the vast majority of
> JDBC prepared statements are only used once.

Is that saying that the performance is reduced with the *new
functionality* more than current PreparedStatement processing, or that
PreparedStatements are in genral, slower?  Sounds like the former, and
your observation that prepared statements are mostly used only once is
kinda troublesome.  Sounds like this feature will need to be
enabled/disabled in the URL (I think I saw a posting about that from
you), to avoid impacting applications that do not re-use prepared
statements.  Our framework allows for re-use, so I am looking forward to
improved performance once we start in earnest the move to 7.3.x.

>
> 4) This is version one of the functionality.  We can learn from it to
> understand how it can be improved for 7.4.

I hope we can help get some feedback to you on this.  Do you have a
timeline for 7.4 features?

Thanks,

Charlie