Re: Statement Pooling

On Thursday 27 May 2010 16:56:28 you wrote:
> > 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?
>
> That's the idea.

Really? Today I think c3p0 statement pooling it is not only about saving time
in object creation, but to let postgresql reuse an already prwpeared
statement.

> > As we use about 100 different statements I would set
> > c3p0.maxStatementsPerConnection = 100
> >
> > Is this reasonable? Is there a real benefit activating it?
>
> Answering that question for your situation really requires benchmarking
> with and without statement caching turned on. Your best bet is probably to
> set it to a value that seems decent, and revisit it if you find a
> performance bottleneck you need to resolve which looks like it's related to
> statement caching.

Ok thanks for your detailed answer. I did some benchmarking and it seems to be
faster with c3p0 statement cache.

> > 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 don't think so, though I may be wrong. The change you refer to replans
> such things when the actual objects change, such as when you remove a
> column or something that would make the plan fail to execute.

Ok, so i should be aware of this. if the execution plan postgresql is caching
gets old, i can run into trouble.

So i should close my connections in the pool from time to time to force
replanning of my prepared statements.

i have asked this question on stackoverflow too, because i thought it was to
jdbc/hibernate specific for this list after sending and not getting an answer.
So for further reference i post a link to this question, where you can find an
excellent answer, too:

http://stackoverflow.com/questions/2920740/should-i-activate-c3p0-statement-
pooling/2921340#2921340

kind regards
Janning

http://www.kicktipp.de/

>
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com