commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Cary Jamison" <Cary_Jami...@Symantec.com>
Subject PreparedStatement Pool performance
Date Thu, 17 Aug 2006 19:53:13 GMT
I'm attempting to add prepared statement pooling to my dbcp connections, but 
I'm not getting the increase in performance I expected.

Here's how I'm doing the comparisons:  I have two loops, in one I get a 
prepared statement and execute it 100 times.  In the other, I get the 
prepared statement within each loop.  With pooling enabled, I would expect 
the performance of the second loop to approach the performance of the first 
loop, but it doesn't seem to improve significantly whether or not I have 
pooling on.

The pooling seems to be working, because I can check in the second loop if 
the innermost delegate statement is the same as the previous time through 
the loop (I found this trick in the mail archives).

My loops are basically :

  // first loop, get the statement once, execute it 100 times
  log.info("start first loop");
  PreparedStatement ps = conn.PrepareStatement("select ...",
    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
  for (int i=0; i<100; ++i)
  {
    // set ps parameters
    ResultSet rs = ps.executeQuery();
    // process rs
    rs.close();
  }
  ps.close();
  log.info("end first loop");

  // second loop, get the statement each time
  log.info("start second loop");
  for (int i=0; i<100; ++i)
  {
    PreparedStatement ps = conn.PrepareStatement("select ...",
      ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    // set ps parameters
    ResultSet rs = ps.executeQuery();
    // process rs
    rs.close();
    ps.close();
  }
  log.info("end second loop");

I'm enabling pooling by simply adding a GenericKeyedObjectPoolFactory to my 
PoolableConnectionFactory.

  poolConnFactory = new PoolableConnectionFactory(mConnFactory, mPool,
    new GenericKeyedObjectPoolFactory(null), // <-- this was null before
    null, false, true);

Is there something more I need to do?  Or are my assumptions unrealistic 
about the performance increase I should see?

Thanks,
Cary




---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message