16.1 Measuring JDBC Performance
As with all other types of tuning, the first step to tuning JDBC usage is to identify bottlenecks. The ease or difficulty of measuring JDBC communications can depend on how you use JDBC and how widespread JDBC calls are in your application. In this section, we'll run through a framework that makes measuring JDBC performance straightforward.
16.1.1 The Core java.sql Interfaces
At the outset, you must identify exactly what you should measure. Effectively profiling distributed applications, such as those that use JDBC, can be difficult. I/O can be significant in profiling simply because of the nature of a distributed application, which normally has threads waiting on I/O. Whether threads blocking on reads and writes are part of a significant bottleneck or simply a side issue can be unclear.
If you look in the java.sql package, three interfaces 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 data in question
The following method illustrates standard database interaction by accessing all the columns from every row of a specified database table and storing the data from each row in 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;
}
The SDK does not include implementations of Connection, Statement, or ResultSet to connect to particular databases in java.sql (except for the more generic JDBC-ODBC driver discussed later). Each implementation for 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 class name of the Connection object or other objects that you are using, you would probably see something like XXXConnection, XXXStatement, XXXConnectionImpl, or XXXStatementImpl, where XXX is the name of the database you are using (Oracle, for example).
To measure the JDBC performance of the getATable( ) method, 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 the process took. That technique works when database interaction is isolated, as it is in this method. However, a Java application usually spreads its database interaction among many methods in many classes, and it is often difficult to isolate the database interaction. You need a way to measure widespread database interactions.
Ideally, the JDBC classes would have built-in measurement capabilities and you could simply turn them on to analyze the performance of database interactions. JDBC classes do not normally provide this feature, but sometimes they do (check the driver documentation). Can we replace the JDBC classes with our own implementations that provide measurements? The replacement classes would have to provide all the current functionality, be able to measure database interactions, require very little change to the application, and be transparent to the rest of the application. That's a tall order.
Fortunately, when a framework is defined almost entirely in terms of interfaces, as JDBC is, it is 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 object-oriented terms) all the method calls to that original class. Here, you can replace JDBC classes with wrappers around the original classes. You can embed your measuring capabilities in the wrapper classes and execute those measurements throughout the application.
16.1.2 Wrapping Classes Using Proxy Objects
Wrapping objects of a particular interface by using dedicated wrapper objects is a useful and established technique. The synchronized wrappers of the Collection classes are probably best known, but there are many other examples. The SDK even has a special class to generate wrapper objects at runtime: java.lang.reflect.Proxy. Wrapper objects are also known as proxy objects. I could have used generated proxy objects here, but that would have made the explanation of wrapping JDBC objects more complicated than I wanted, so I stuck with explicitly coded wrapper classes. Proxy classes are also slightly slower than precompiled classes; for measuring JDBC, though, it probably wouldn't make any difference.
16.1.2.1 Wrapping the Connection class
Start by wrapping the Connection class. The following ConnectionWrapper class implements Connection. The class has one instance variable of Connection type and a constructor that initializes that instance variable with the constructor parameter. Most Connection methods are simply defined to delegate the call to the instance variable:
package tuning.jdbc;
import java.sql.*;
import java.util.Map;
public class ConnectionWrapper implements Connection
{
protected Connection realConnection;
public Connection realConnection ( ) {
return realConnection;
}
public ConnectionWrapper (Connection connection) {
realConnection = connection;
}
public void clearWarnings( ) throws SQLException {
realConnection.clearWarnings( );
}
public void close( ) throws SQLException {
realConnection.close( );
}
public boolean isClosed( ) throws SQLException {
return realConnection.isClosed( );
}
public void commit( ) throws SQLException {
realConnection.commit( );
}
...
I have left out most methods, but they follow the template of the ones shown here. Where you would have used a Connection object obtained from the database driver, instead simply wrap that Connection object with the ConnectionWrapper and use the ConnectionWrapper object. Wherever you obtain your Connection object, simply add one extra line such as the one shown here in bold:
Connection dbConnection = getConnectionFromDriver( );
dbConnection = new ConnectionWrapper(dbConnection);
Obtaining connections is really the only part of the application that needs changing, so find and edit calls that obtain a connection. Most applications use a central factory class to provide Connection objects, making it simple to add the ConnectionWrapper. The factory class frequently fronts a pool of connections, and little extra work is necessary to release the connection back into the pool since it first needs to be unwrapped:
public static void releaseConnection(Connection conn)
{
if (conn instanceof ConnectionWrapper)
conn = ( (ConnectionWrapper) conn).realConnection( );
//carry on with original release code
...
}
You haven't actually finished the ConnectionWrapper class yet. Some methods of the ConnectionWrapper class are not simple delegations. These methods provide various types of Statement objects:
public Statement createStatement( ) throws SQLException {
return new StatementWrapper(realConnection.createStatement( ), this);
}
public Statement createStatement(int resultSetType,
int resultSetConcurrency) throws SQLException {
return new StatementWrapper(
realConnection.createStatement(resultSetType,
resultSetConcurrency), this);
}
public CallableStatement prepareCall(String sql) throws SQLException {
return new CallableStatementWrapper(
realConnection.prepareCall(sql), this, sql);
}
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
return new CallableStatementWrapper(
realConnection.prepareCall(sql, resultSetType,
resultSetConcurrency), this, sql);
}
public PreparedStatement prepareStatement(String sql)
throws SQLException {
return new PreparedStatementWrapper(
realConnection.prepareStatement(sql), this, sql);
}
public PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
return new PreparedStatementWrapper(
realConnection.prepareStatement(sql, resultSetType,
resultSetConcurrency), this, sql);
}
As you can see, you must define three types of Statement wrapper classes. In addition, you need another wrapper class for DatabaseMetaData; this wrapper class is required because DatabaseMetaData can return the Connection object used to create the DatabaseMetaData. Therefore, you need to make sure that the Connection object is wrapped, not the original unwrapped connection:
public DatabaseMetaData getMetaData( ) throws SQLException {
return new DatabaseMetaDataWrapper(
realConnection.getMetaData( ), this);
}
16.1.2.2 Wrapping the Statement classes
The three statement classes, Statement , PreparedStatement, and CallableStatement, have similar simple wrappers that forward all calls:
public class StatementWrapper implements Statement
{
protected Statement realStatement;
protected ConnectionWrapper connectionParent;
public StatementWrapper(Statement statement, ConnectionWrapper parent)
{
realStatement = statement;
connectionParent = parent;
}
public void cancel( ) throws SQLException {
realStatement.cancel( );
}
...
You can implement PreparedStatementWrapper as a subclass of StatementWrapper, but it isn't a requirement. You could implement PreparedStatement as a subclass of Object and implement all the required methods rather than inherit the Statement methods:
public class PreparedStatementWrapper extends StatementWrapper implements
PreparedStatement
{
PreparedStatement realPreparedStatement;
String sql;
public PreparedStatementWrapper(PreparedStatement statement, ConnectionWrapper
parent, String sql)
{
super(statement, parent);
realPreparedStatement = statement;
this.sql = sql;
}
public void addBatch( ) throws SQLException {
realPreparedStatement.addBatch( );
}
...
Similarly, you can implement the CallableStatementWrapper as a subclass of PreparedStatementWrapper:
public class CallableStatementWrapper extends PreparedStatementWrapper implements
CallableStatement
{
CallableStatement realCallableStatement;
public CallableStatementWrapper(CallableStatement statement, ConnectionWrapper
parent, String sql)
{
super(statement, parent, sql);
realCallableStatement = statement;
}
public Array getArray(int i) throws SQLException {
return new SQLArrayWrapper(realCallableStatement.getArray(i), this, sql);
}
You still haven't quite finished. Several kinds of methods in these Statement wrapper classes should not be simple delegations. First, there is a method that returns the Connection object. You want to return the ConnectionWrapper instead. Here is the method from StatementWrapper:
public Connection getConnection( ) throws SQLException {
return connectionParent;
}
Second, some methods return ResultSets. These methods need to return ResultSet wrappers. To keep the ResultSetWrapper consistent, I've added a lastSql String instance variable to StatementWrapper, which is passed to the ResultSetWrapper constructor. This instance variable is useful when assigning measurements to particular SQL statements. The methods that return ResultsSets are:
//StatementWrapper method
public ResultSet getResultSet( ) throws SQLException {
return new ResultSetWrapper(realStatement.getResultSet( ), this, lastSql);
}
public ResultSet executeQuery(String sql) throws SQLException {
return new ResultSetWrapper(realStatement.executeQuery(sql), this, sql);
}
//PreparedStatementWrapper method
public ResultSet executeQuery( ) throws SQLException {
return new ResultSetWrapper(realPreparedStatement.executeQuery( ), this, sql);
}
Third, some methods use java.sql.Array objects. Because these Array objects can return a ResultSet, you again need to provide an Array wrapper so ResultSetWrapper objects rather than plain ResultSets are returned. You also need to handle the case in which an Array object is passed into the setArray( ) method: if it is an Array wrapper, the object needs to be unwrapped before being passed to the underlying PreparedStatement:
public void setArray(int i, Array x) throws SQLException {
if (x instanceof SQLArrayWrapper)
realPreparedStatement.setArray(i, ((SQLArrayWrapper) x).realArray);
else
realPreparedStatement.setArray(i, x);
}
public Array getArray(int i) throws SQLException {
return new SQLArrayWrapper(realCallableStatement.getArray(i), this, sql);
}
Finally, the reason why you create all these wrapper classes is to enable measurements to be taken. The methods that execute the SQL statements, reasonably enough, start with execute. You need to add logging to these methods. Note that I delegate responsibility for logging to a JDBCLogger class in the following methods. Essentially, each method has a call to the real execute method wrapped with a logging call. I pass the SQL string and the current thread to the logging call because both are very useful parameters for any type of logging, especially for measuring the time taken for the procedure to run. I also redefine the executeQuery( ) methods that return ResultSets (which were first defined a couple of code fragments back) so they now perform logging:
//StatementWrapper methods
public void addBatch(String sql) throws SQLException {
realStatement.addBatch(sql);
lastSql = sql;
}
public boolean execute(String sql) throws SQLException {
Thread t = Thread.currentThread( );
JDBCLogger.startLogSqlQuery(t, sql);
boolean b = realStatement.execute(sql);
JDBCLogger.endLogSqlQuery(t, sql);
lastSql = sql;
return b;
}
public int[ ] executeBatch( ) throws SQLException {
Thread t = Thread.currentThread( );
JDBCLogger.startLogSqlQuery(t, "batch");
int[ ] i = realStatement.executeBatch( );
JDBCLogger.endLogSqlQuery(t, "batch");
return i;
}
public ResultSet executeQuery(String sql) throws SQLException {
Thread t = Thread.currentThread( );
JDBCLogger.startLogSqlQuery(t, sql);
ResultSet r = realStatement.executeQuery(sql);
JDBCLogger.endLogSqlQuery(t, sql);
lastSql = sql;
return new ResultSetWrapper(r, this, sql);
}
public int executeUpdate(String sql) throws SQLException {
Thread t = Thread.currentThread( );
JDBCLogger.startLogSqlQuery(t, sql);
int i = realStatement.executeUpdate(sql);
JDBCLogger.endLogSqlQuery(t, sql);
lastSql = sql;
return i;
}
//PreparedStatementWrapper methods
public boolean execute( ) throws SQLException {
Thread t = Thread.currentThread( );
JDBCLogger.startLogSqlQuery(t, sql);
boolean b = realPreparedStatement.execute( );
JDBCLogger.endLogSqlQuery(t, sql);
return b;
}
public ResultSet executeQuery( ) throws SQLException {
Thread t = Thread.currentThread( );
JDBCLogger.startLogSqlQuery(t, sql);
ResultSet r = realPreparedStatement.executeQuery( );
JDBCLogger.endLogSqlQuery(t, sql);
return new ResultSetWrapper(r, this, sql);
}
public int executeUpdate( ) throws SQLException {
Thread t = Thread.currentThread( );
JDBCLogger.startLogSqlQuery(t, sql);
int i = realPreparedStatement.executeUpdate( );
JDBCLogger.endLogSqlQuery(t, sql);
return i;
}
16.1.2.3 Wrapping the ResultSet class
The ResultSetWrapper class once again consists mainly of delegated methods:
public class ResultSetWrapper implements ResultSet
{
ResultSet realResultSet;
StatementWrapper parentStatement;
String sql;
public ResultSetWrapper(ResultSet resultSet, StatementWrapper statement, String sql) {
realResultSet = resultSet;
parentStatement = statement;
this.sql = sql;
}
public boolean absolute(int row) throws SQLException {
return realResultSet.absolute(row);
}
...
Again, some methods are not plain delegations. The getStatement( ) method returns the statement that generated this ResultSet. You need to return the StatementWrapper:
public Statement getStatement( ) throws SQLException {
return parentStatement;
}
The getArray( ) methods need to return a wrapped Array object:
public Array getArray(int i) throws SQLException {
return new SQLArrayWrapper(realResultSet.getArray(i), parentStatement, sql);
}
public Array getArray(String colName) throws SQLException {
return new SQLArrayWrapper(realResultSet.getArray(colName), parentStatement, sql);
}
Finally, you need to add logging. Many developers erroneously believe that the various Statement.execute methods incur the main cost of the database interaction. This is true for database updates and for database reads that involve only a few rows. However, if more than a few rows of a query are read, then the ResultSet.next( ) method can spend a lot of time fetching rows from the database. If many rows are to be read, ResultSet.next( ) calls can take much more cumulative time than the execution of the SQL statements. Log ResultSet.next( ) calls so you can track them:
public boolean next( ) throws SQLException {
Thread t = Thread.currentThread( );
JDBCLogger.startLogSqlNext(t, sql);
boolean b = realResultSet.next( );
JDBCLogger.endLogSqlNext(t, sql);
return b;
}
You can log other ResultSet calls if needed, such as previous( ), insertRow( ), etc. But most applications need only next( ) logged, and that is how I'll leave the class.
16.1.2.4 The JDBC wrapper framework
That pretty much covers the classes that need wrapping. I haven't explicitly shown the Array wrapper or the DatabaseMetaData wrapper, but they are straightforward, needing only delegation and ResultSetWrappers and ConnectionWrappers returned instead of ResultSets and Connections. All the classes and wrapper classes for JDBC 2 can be obtained from the web site for this book, http://www.oreilly.com/catalog/javapt2. JDBC Versions 1, 2, and 3 are all amenable to using wrapper objects to measure database interaction, though they differ slightly in their interface definitions and thus require different wrapper classes. However, all the wrapper classes for the different versions can be created the same way, following the procedure covered in this chapter.
The class I haven't yet shown you is the JDBCLogger class. A simple implementation of that class would have null calls for the logging methods, providing no logging:
package tuning.jdbc;
public class JDBCLogger
{
public static void startLogSqlQuery(Thread t, String sql) { }
public static void endLogSqlQuery(Thread t, String sql) { }
public static void startLogSqlNext(Thread t, String sql) { }
public static void endLogSqlNext(Thread t, String sql) { }
}
Timing the queries would be more useful. The following methods work by holding the start time of the query and finding the elapsed time for the query when it finishes. The methods are kept simple by assuming that SQL queries cannot recurse within the same thread, an assumption that is generally true:
private static Hashtable QueryTime = new Hashtable( );
public static void startLogSqlQuery(Thread t, String sql)
{
if (QueryTime.get(t) != null)
System.out.println("WARNING: overwriting sql query log time for "
+ sql);
QueryTime.put(t, new Long(System.currentTimeMillis( )));
}
public static void endLogSqlQuery(Thread t, String sql)
{
long time = System.currentTimeMillis( );
time -= ((Long) QueryTime.get(t)).longValue( );
System.out.println("Time: " + time + " milliseconds for SQL query " + sql);
QueryTime.remove(t);
}
Using these methods in the JDBCLogger class provides output lines similar to the following code for every SQL query execution:
Time: 53 milliseconds for SQL query SELECT * FROM JACKTABL
This output gives you precise measurements for each SQL query. You can also sum the queries in the JDBCLogger class, retain the maximum half-dozen or so measurements, and then print out a summary statement. The summary measurements I usually like to have are minimum, maximum, average, standard deviation, and the 90th percentile value. Summary values tend to be more useful for a production environment, whereas printing individual lines for each query is more useful when profiling the system during development.
16.1.2.5 Using the JDBC wrapper framework
Wrappers are very useful for determining the performance of JDBC calls in both development and deployed applications. Because they are simple and robust and require very little alteration to the application, wrappers can be retained in a deployed application. Creating a configurable JDBCLogger class lets you turn logging on and off at will.
During development, these classes enable you to identify individually expensive database interactions and repeated database interactions that are expensive because of their cumulative cost. Identifying these expensive database interactions is the first step toward improving your application's performance. In production, these wrapper classes identify discrepancies between expected performance and reality.
After you use these classes and techniques to pinpoint where JDBC is causing a performance problem, you need to tune the database interactions. The next section addresses performance-tuning techniques for JDBC.
