Re: Very strange performance decrease when reusing a PreparedStatement

On Sun, May 3, 2009 at 12:10 PM, Dave Cramer <pg@fastcrypt.com> wrote:
>
>> It appears that the Postgres "server-prepared statement" cannot handle
>> parameters to the statement. This is really unfortunate, because 99%
>> of real-life applications will want to re-use the same statement
>> (template) with different parameters.
>>
>> The term "server-prepared statement" itself already indicates that
>> there may be something skewed about the "local" semantics of
>> java.sql.PreparedStatements in the Postgres JDBC driver. There is no
>> notion of "client-prepared statement" in the JDBC API, which conceives
>> PreparedStatement instances as mere handles to server side objects.
>> And indeed, Postgres JDBC users have historically been using
>> java.sql.PreparedStatements for its side-effect of preventing SQL
>> injection rather than for the purpose the JDBC API designers had in
>> mind with this class.
>
> I'm not sure where this hypothesis is coming from. Postgresql server
> prepared statements can certainly handle parameters.
>
> What makes you think it can't ?

http://jdbc.postgresql.org/documentation/83/server-prepare.html:

"Server side prepared statements are planned only once by the server.
This avoids the cost of replanning the query every time, but also
means that the planner cannot take advantage of the particular
parameter values used in a particular execution of the query. You
should be cautious about enabling the use of server side prepared
statements globally."

Or is this only the JDBC perspective of things, meaning that the
capability is there at the back-end, just the JDBC driver doesn't take
advantage of it?

Thanks
Peter

>
> Dave
>
>