Re: Very strange performance decrease when reusing a PreparedStatement

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) !


Best regards,
--
Frédérik Bilhaut
NOOPSIS