Checking that Prepared statements are being reused

[Paul Taylor-9]

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




------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
c3p0-users mailing list
c3p0-users@...
https://lists.sourceforge.net/lists/listinfo/c3p0-users

[Steve Waldman]

On Oct 28, 2009, at 12:21 PM, Paul Taylor wrote:

> 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.

fully implemented in c3p0

> 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.

yes, c3p0 as long as the parameters defining the prepared statement  
are identical (usually this just means the sql text is the same, but  
you might have used one of the several versions of prepareStatement  
that set other params, and all these parameters are checked), c3p0  
will retrieve the previously constructed PreparedStatement object  
internally.

> 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.

NewProxyConnection.prepareStatement(...) ->  
NewPooledConnection.checkoutStatement(...) ->  
GooGooStatementCache.checkoutStatement( ... )

> 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)

should be fine. but check the dump of config on initialization (which  
c3p0 logs at INFO, wherever you are logging stuff to), and make sure  
that maxStatementsPerConnection is actually 30.

> 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.

let c3p0 take care of this for you, just make sure  
maxStatementsPerConnection is large enough so you don't churn through  
prepared statements.

>
> thanks Paul
>
>
>

good luck!
      Steve



------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
c3p0-users mailing list
c3p0-users@...
https://lists.sourceforge.net/lists/listinfo/c3p0-users

[Paul Taylor-9]

Steve Waldman wrote:
>
> On Oct 28, 2009, at 12:21 PM, Paul Taylor wrote:
>> 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.
>
> NewProxyConnection.prepareStatement(...) ->
> NewPooledConnection.checkoutStatement(...) ->
> GooGooStatementCache.checkoutStatement( ... )
>
Thx yes it was working (I think the issue is somewhere else after all),
I then tried disabling maxStatementsPerConnection, then the path becomes
NewProxyConnection.prepareStatement(...) ->
Embedconnection.prepareStatement(...)

One thing I don't get is that with my program I d expect there to be one
path of :
    NewProxyConnection.prepareStatement(...) ->
Embedconnection.prepareStatement(...)
Per  pooled connection using this statement (for when it initially gets
created)
then
   NewProxyConnection.prepareStatement(...) ->
NewPooledConnection.checkoutStatement(...) ->
GooGooStatementCache.checkoutStatement( ... )
for the remaining statements but this doesn't seem to happen


I call a particular prepared statement for every file I load into the
program, its the same code calling the prepared statement so is
identical every time.
Running against 464 files with a profiler (Yourkit Java Profiler) I can see:

Songcache.addToDatabase : 464 calls (my method)
NewProxyConnection.prepareStatement(): 464 calls
NewPooledConnection.checkoutStatement(): 464 calls
GooGooStatementCache.checkoutStatement(): 464 calls
NewProxyConnection.executeUpdate():464 calls

there were no calls to Embedconnection.prepareStatement(...) !

Paul

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
c3p0-users mailing list
c3p0-users@...
https://lists.sourceforge.net/lists/listinfo/c3p0-users

[Steve Waldman]

Paul,

The Statement cache prepares the actual Statement asynchronously (but  
remaining careful of concurrency issues -- and there is still a race  
condition that bites very rarely and primary Oracle clients here).  
When you "prepare" a Statement the first time, you tell the cache what  
it should prepare asynchronously, and then wait (in checkout) until it  
is ready. Thereafter, it is recovered from some kind of hashtable as  
you would expect.

       smiles,
           Steve


On Oct 29, 2009, at 5:28 AM, Paul Taylor wrote:

> Steve Waldman wrote:
>>
>> On Oct 28, 2009, at 12:21 PM, Paul Taylor wrote:
>>> 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.
>>
>> NewProxyConnection.prepareStatement(...) ->  
>> NewPooledConnection.checkoutStatement(...) ->  
>> GooGooStatementCache.checkoutStatement( ... )
>>
> Thx yes it was working (I think the issue is somewhere else after  
> all), I then tried disabling maxStatementsPerConnection, then the  
> path becomes
> NewProxyConnection.prepareStatement(...) ->  
> Embedconnection.prepareStatement(...)
>
> One thing I don't get is that with my program I d expect there to be  
> one path of :
>   NewProxyConnection.prepareStatement(...) ->  
> Embedconnection.prepareStatement(...)
> Per  pooled connection using this statement (for when it initially  
> gets created)
> then
>  NewProxyConnection.prepareStatement(...) ->  
> NewPooledConnection.checkoutStatement(...) ->  
> GooGooStatementCache.checkoutStatement( ... )
> for the remaining statements but this doesn't seem to happen
>
>
> I call a particular prepared statement for every file I load into  
> the program, its the same code calling the prepared statement so is  
> identical every time.
> Running against 464 files with a profiler (Yourkit Java Profiler) I  
> can see:
>
> Songcache.addToDatabase : 464 calls (my method)
> NewProxyConnection.prepareStatement(): 464 calls
> NewPooledConnection.checkoutStatement(): 464 calls
> GooGooStatementCache.checkoutStatement(): 464 calls
> NewProxyConnection.executeUpdate():464 calls
>
> there were no calls to Embedconnection.prepareStatement(...) !
>
> Paul

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
c3p0-users mailing list
c3p0-users@...
https://lists.sourceforge.net/lists/listinfo/c3p0-users