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
