[c3p0-users] Checking that Prepared statements are being reused

[Paul T. <pau...@fa...>]

Hi

I use prepared statements throughout my application , Im using c3po with 
Apache Derby and I'm not convinced that the prepared statements are 
being reused as my db calls appear to be a bottleneck in the code. Ive 
tried looking at  Cached Prepared Statements  in general and the 
documentation is very woolly.  Most examples centre on a prepared stmt 
being created once and then reused in a loop, I don't have this scenario 
but particular prepared statements will be called many times through the 
lifetime of the application. If someone could answer some of these 
questions that would be great.

1. Is c3po's prepared statement caching dependent on whether statement 
caching is supported in the underlying jdbc driver or is it implemented 
fully in c3po itself.
2. Is it implicit, in my code I recreate the prepared statement every 
time, but Im hoping the that behind the scenes c3po just retrives the 
previously compiled one and reuses it.
3. I have a profiler/debugger , is there something I could look for in 
the stack to identify whether or not its reusing the statement.
4. This is how I open the pooling, as I understand it c3po will cache 
the last thirty prepared statements on any particular connection, and 
allows up to 50 connections, correct ?
DataSource unpooled = 
DataSources.unpooledDataSource("jdbc:derby:/user/paul/testdb","paul","paul")
Map overrides = new HashMap();
overrides.put("maxStatementsPerConnection","30");
overrides.put("maxPoolSize","50");
DataSource pooled = DataSources.pooledDataSources( unpooled, overrides)
5. In another project which doesn't use pooling and is single threaded 
and only uses a single db connection, I explicitly compiled all the 
prepared statements once, and then added them to a hashmap. Then they 
were retrieved as required and executed but never closed (closing 
prevents them being reused). This improved performance by about 20% but 
would be difficult to code in my current multithreaded application. In 
the new app  connections are only grabbed when a query has to be 
executed, and then the connection is closed straight away would it be 
worth actually getting each thread to hang onto their connection for the 
life of the app, and would it be worth explicity caching  the most 
comonly used prepared statements for each connection and then reusing them.

thanks Paul