Re: Very strange performance decrease when reusing a PreparedStatement

2009/4/29 Frédérik Bilhaut <frederik(dot)bilhaut(at)noopsis(dot)fr>:
> Le 28 avr. 09 à 17:21, Oliver Jowett a écrit :
>>
>> Try with prepareThreshold=0. Probably, your particular query benefits
>> from re-planning each time with the particular concrete parameter values
>> for each execution.
>>
>> (you can either specify this as a URL parameter, or tweak it on a
>> per-connection or per-statement basis via methods on
>> PGConnection/PGStatement)
>
>
> Thank you Oliver for this answer.
>
> Your hypothesis seems plausible to me, because we rely strongly on indexes
> to improve performance, and it appears that the query duration of a reused
> satement is the that the same query without index. On the other hand,
> "explain analyze" tells that the index is correctly used each time, but it
> also reports a very short total time in any case, so who knows what
> happens...
>
> Anyway, I will try the option you suggest asap (although re-creating
> statements each time does not seem to be so harmful...).
>
> But there is maybe something somewhere in the driver (or pg iteself ?) that
> may have to be fixed or at least documented ? Every JDBC optimisation
> tutorial will mention the fact that preparing and reusing statements can
> improve the performances of recurrent query, but my example proves that in
> some situations, the performances can be dramatically worsen (around 20
> times slower in my case) !
>

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.

Peter

>
> Best regards,
> --
> Frédérik Bilhaut
> NOOPSIS
>
>
>
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>