JDBC resultset close

Question

I am doing profiling of my Java application and found some interesting statistics for a jdbc PreparedStatement call:

Given below is the environment details: Database: Sybase SQL Anywhere 10.0.1 Driver: com.sybase.jdbc3.jdbc.SybDriver connection pool: c3p0 JRE: 1.6.0_05

The code in question is given below:

try {
    ps = conn.prepareStatement(sql);
    ps.setDouble(...);
    rs = ps.executeQuery();
              ......

    return xyz;
}
finally {
    try {
        if (rs != null) rs.close();
        if (ps != null) ps.close();
    }
    catch (SQLException sqlEx) {

    }
}

From JProfiler stats, I find that this particular resultspace.close() statement alone takes a large amount of time. It varies from 25 ms to 320s while for other code blocks which are of identical in nature, i find that this takes close to 20 microseconds.

Just to be sure, I ran this performance test multiple times and confirmed this data. I am puzzled by this behaviour - Thoughts?

Answer 1

Is the method call actually causing CPU load during the delay or is it simply waiting? Closing the ResultSet most likely involves remote communication with the database and my guess is that there are some circumstances where this may take a while.

Answer 2

This performance is JDBC driver specific. The C3P0 connection pool should not have any influence on it. I would suggest to retest it with a newer or another JDBC driver. An alternative to the Sybase driver is the jTDS driver. I am not sure how this performs compared to the Sybase driver, but it is known to be very performant as compared to Microsoft's own MSSQL JDBC driver.

Unrelated to the actual problem, you should in fact call the close() methods each in its own try block, else there's no guarantee that they will all be closed. If the first close throws SQLException, the subsequent close calls won't be executed. The Apache Commons DbUtils may help to take the boilerplate code away.

Answer 3

On a semi-related note, check out Apache Commons DbUtils and the Dbutils.closeQuietly() method for easily managing closing of connections/statements/resultsets in the correct order with correct exception handling.

Answer 4

If the statement is a select and you are not consuming all the data try canceling the statement before closing it.