commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Todd Carmichael <to...@concur.com>
Subject [dbcp]SharedPoolDataSource.setupDefaults REALLY hurts performance /scalability on Oracle
Date Sat, 06 Mar 2004 22:24:35 GMT
Our stress tests have shown that the following method in
SharedPoolDataSource really hampers performance on Oracle and to a lesser
extent SQL Server.  The problem is that calling setAutoCommit and
setReadOnly incurs the cost of a network round trip to the database server.
So the cost of getting a connection from the pool requires two round trips
to the database server before a query is even made.  

    protected void setupDefaults(Connection con, String username)
        throws SQLException {

        con.setAutoCommit(isDefaultAutoCommit());
        con.setReadOnly(isDefaultReadOnly());
        int defaultTransactionIsolation = getDefaultTransactionIsolation();
        if (defaultTransactionIsolation != UNKNOWN_TRANSACTIONISOLATION) {
            con.setTransactionIsolation(defaultTransactionIsolation);
        }
    }

I am not proposing this has a general purpose solution but in our
implementation of dbc code, we are going to change the method to the
following:

    protected void setupDefaults(Connection con, String username)
        throws SQLException {

        if (con.getAutoCommit() != isDefaultAutoCommit())
        {
            con.setAutoCommit(isDefaultAutoCommit());
        }
        int defaultTransactionIsolation = getDefaultTransactionIsolation();
        if (defaultTransactionIsolation != UNKNOWN_TRANSACTIONISOLATION) {
            con.setTransactionIsolation(defaultTransactionIsolation);
        }
    }

Our application never employs readonly connections and con.getAutoCommit
does not do a network hit.  With this change, performance/scalability of
commons/dbcp and commons/pool matches that of connection pooling provided by
Weblogic and other appservers.  

Enjoy as you wish.  
ToddC


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message