Is there any setting I'd need to tweak assuming I'm using 150-200 of these at once?
Cheers
Dave
On Wed, Apr 14, 2010 at 6:10 PM, Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> wrote:
On 15/04/10 04:49, Dave Crooke wrote:Pg supports real server-side prepared statements, as does the JDBC driver.
Hi foilks
I am using PG 8.3 from Java. I am considering a performance tweak which
will involve holding about 150 java.sql.PreparedStatment objects open
against a single PGSQL connection. Is this safe?
I know that MySQL does not support prepared statements /per se/, and so
their implementation of PreparedStatement is nothing more than some
client-side convenience code that knows how to escape and format
constants for you. Is this the case for PG, or does the PG JDBC driver
do the real thing?
IIRC (and I can't say this with 100% certainty without checking the sources or a good look at TFM) the PostgreSQL JDBC driver initially does only a client-side prepare. However, if the PreparedStatement is re-used more than a certain number of times (five by default?) it switches to server-side prepared statements.
This has actually caused a bunch of performance complaints on the jdbc list, because the query plan may change at that switch-over point, since with a server-side prepared statement Pg no longer has a specific value for each parameter and may pick a more generic plan.
Again only IIRC there's a configurable threshold for prepared statement switch-over. I thought all this was in the PgJDBC documentation and/or javadoc - if it's not, it needs to be.
--
Craig Ringer
