Skip site navigation (1) Skip section navigation (2)

Re: Very strange performance decrease when reusing a PreparedStatement

From: Péter Kovács <maxottovonstirlitz(at)gmail(dot)com>
To: Dave Cramer <pg(at)fastcrypt(dot)com>
Cc: Frédérik Bilhaut <frederik(dot)bilhaut(at)noopsis(dot)fr>, Oliver Jowett <oliver(at)opencloud(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Very strange performance decrease when reusing a PreparedStatement
Date: 2009-05-03 16:28:53
Message-ID: fdeb32eb0905030928j4e72f1e3vbecb78d0cb74be58@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
On Sun, May 3, 2009 at 12:10 PM, Dave Cramer <pg(at)fastcrypt(dot)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
>
>

In response to

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2009-05-03 16:42:52
Subject: Re: Unit test patches
Previous:From: Tom LaneDate: 2009-05-03 14:41:36
Subject: Re: Very strange performance decrease when reusing a PreparedStatement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group