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: Frédérik Bilhaut <frederik(dot)bilhaut(at)noopsis(dot)fr>
Cc: 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 08:22:42
Message-ID: fdeb32eb0905030122t665113e5nd34e7268d9ab1863@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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
>

In response to

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2009-05-03 10:10:41
Subject: Re: Very strange performance decrease when reusing a PreparedStatement
Previous:From: Oliver JowettDate: 2009-05-02 23:35:55
Subject: Re: getTiIme/Timestamp with TimeZone inconsistency

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