Tuning JDBC: Measuring JDBC performance

Java Database Connectivity (JDBC) is used extensively by many Java applications. In this article, the first of a series on performance-tuning JDBC, I look at how to measure the performance of JDBC and how to identify which parts of your JDBC subsystem need optimizing.

The core java.sql interfaces

Our goal is to improve the performance of our application. Normally, your first step should be to profile the application to find the bottlenecks, and then address those bottlenecks. However, effectively profiling distributed applications can be difficult. I/O can show up as significant in profiling, simply because of the nature of a distributed application, which normally has threads spending time waiting on I/O. It can be unclear whether threads blocking on reads and writes are part of a significant bottleneck or simply a side issue. When profiling, it is usually worthwhile to have separate measurements available for the communication subsystems. So if we want to measure the performance of the JDBC subsystem, what do we measure?

If you look in the java.sql package, there are three interfaces that form the core of JDBC: Connection, Statement, and ResultSet. Normal interaction with a database consists of:

• Obtaining a Connection object from the database driver
• Obtaining from that Connection object some type of Statement object capable of executing a particular SQL statement
• If that SQL statement reads from the database, using the Statement object to obtain a ResultSet object that provides access to the database data.

The following example method illustrates standard database interaction by accessing all of the columns from every row of a specified database table and storing the data from each row into a String [] array, putting all the rows in a vector:

  public static Vector getATable(String tablename, Connection Connection)
    throws SQLException
  {
    String sqlQuery = "SELECT * FROM " + tablename;
    Statement statement = Connection.createStatement();
    ResultSet resultSet = statement.executeQuery(sqlQuery);
    int numColumns = resultSet.getMetaData().getColumnCount();
    String[] aRow;
    Vector allRows = new Vector();
    while(resultSet.next())
    {
      aRow = new String[numColumns];
      for (int i = 0; i < numColumns; i++)
        //ResultSet access is 1-based, arrays are 0-based
        aRow[i] = resultSet.getString(i+1);
      allRows.addElement(aRow);
    }
    return allRows;
  }

There are no concrete implementations of Connection, Statement, or ResultSet in java.sql, or elsewhere in the public SDK. Each implementation of these and other JDBC interfaces is created by the producer of the database driver, and delivered as part of the database driver package. If you printed out the classname of the Connection object or other objects that you are using, you would probably see something like XXXConnection, XXXStatement, or XXXConnectionImpl, XXXStatementImpl, etc., where XXX would be the name of the database you are using, for example, Oracle.

If we wanted to measure the JDBC performance of the example getATable() method just presented, we could simply put calls to System.currentTimeMillis() at the beginning and end of the getATable() method, and print the time difference to find out how long it took. That technique would work where the database interaction is isolated, as in the getATable() method. But usually a Java application spreads its database interaction among many methods in many classes, and it is often difficult to isolate the database interaction. How can we measure database interactions in this more widespread situation?

One ideal way would be if all the JDBC classes had measurement capabilities built into them. Then we could simply turn on measurements whenever we wanted to see the performance of the database interactions. JDBC classes do not normally provide this feature, but perhaps we can replace them with classes that do. Our target is for the replacement classes to provide exactly the functionality previously available, to have the additional capability of measuring database interactions, and also for the replacement to require very little change to and be transparent to the rest of the application. That's a tall bill.

Fortunately, when a framework is defined almost entirely in terms of interfaces, as JDBC is, it becomes very simple to replace any class with another implementation. That is, after all, the whole point of interfaces. In particular, you can always replace any implementation of an interface with a wrapper class that simply wraps the original class and forwards (or delegates, in OO speak) all of the method calls to that original class. Here, we can replace the JDBC classes we use with wrappers which wrap the original classes. We can embed our measuring capabilities in the wrapper classes and have those measurements execute throughout the application.