it's not possible, in a general way, to "detach" PreparedStatements from parent Connections and reattach them to new ones. (It may be possible within some JDBC driver implementations. i've never encountered it, but it is conceivable.)

so PreparedStatements can't be pooled as global resource (by a vendor non-specific library). when they are cached, they must remain associated with their parent Connections, available for reuse when the same, pooled physical Connection is asked to prepare a Statement it has already seen.

what is cached, though, are the GENERAL PreparedStatements, before any parameters are bound. when deciding how many PreparedStatements to cache, you want to count how many distinct queries are frequently used. it doesn't matter if a Statement is reused with a million different rebindings of parameters; it remains a single PreparedStatement.

i hope this helps!


Steve Waldman

On Sep 28, 2013, at 7:19 PM, j fill <jfill99@yahoo.com> wrote:

In our multi-user Tomcat application there is a loop that obtains a connection from the pool, creates a preparedStatement (PS), uses it, and then closes() the PS and the connection. Being a loop the PS is invoked with different run time argument values each time. 

From the c3p0 documentation:
 maxStatements defines the total number PreparedStatements a DataSource will cache. 
This sounds simple, but it's actually a strange approach, because cached statements conceptually belong to individual Connections; they are not global resources.
To figure out a size for maxStatements that does not "churn" cached statements, you need to consider the number of frequently used PreparedStatements in your application, and multiply that by the number of Connections you expect in the pool (maxPoolSize in a busy application).

Since the PS is code that is executed after being bound with the run time arguments, does each connection need a separate copy of the PS? Or is there a separate preparedStatement cache for each connection? Shouldn't preparedStatements be in a "global" cache that any pooled connection can use when needed? In which case only a global maxStatement limit is needed?  I guess I am a little confused by the interplay between maxStatements and maxStatementsPerConnection.

October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
c3p0-users mailing list