Hi,
we are running java6/hibernate/c3p0/postgresql stack.
Our JDBC Driver is 8.4-701.jdbc3
I have a few questions about Prepared Statements. I have read
<http://www.theserverside.com/news/1365244/Why-Prepared-Statements-are-
important-and-how-to-use-them-properly>
At the moment we have
c3p0.maxStatements = 0
c3p0.maxStatementsPerConnection = 0
I don't know if I do understand statement pooling properly, so please help me.
In my understanding it is like this:
Our hibernate stack uses prepared statements. Postgresql is caching the
execution plan. Next time the same statement is used, postgresql reuses the
execution plan. This saves time planning statements inside DB.
Additionally c3p0 can cache java instances of "java.sql.PreparedStatement"
which means it is caching the java object. So when using
c3p0.maxStatementsPerConnection = 100 it caches at most 100 different
objects. It saves time on creating objects, but this has nothing to do with
the postgresql database and its prepared statements.
Right?
As we use about 100 different statements I would set
c3p0.maxStatementsPerConnection = 100
Is this reasonable? Is there a real benefit activating it?
I remember postgresql 8.4 is replanning prepared statements when statistics
change occur, but I didn't find it in the release notes. It is just saying
"Invalidate cached plans when referenced schemas, functions, operators, or
operator classes are modified". Does PG replans prepared statements from time
to time if underlying data statistics change?
I am glad if you help me and give me some insights to managing my connection
pool the right way.
kind regards
Janning
PS: I think its ok to post this on general and not on pgsql-jdbc as I am not
subcribed to pgsql-jdbc. If not, please let me know and I will repost there.
