Tuning JDBC: Measuring JDBC performanceby Jack Shirazi, author of Java Performance Tuning
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.
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:
ResultSet. Normal interaction with a database consists of:
- Obtaining a
Connectionobject from the database driver
- Obtaining from that
Connectionobject some type of
Statementobject capable of executing a particular SQL statement
- If that SQL statement reads from the database, using the
Statementobject to obtain a
ResultSetobject 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)
String sqlQuery = "SELECT * FROM " + tablename;
Statement statement = Connection.createStatement();
ResultSet resultSet = statement.executeQuery(sqlQuery);
int numColumns = resultSet.getMetaData().getColumnCount();
Vector allRows = new Vector();
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);
There are no concrete implementations of
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
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
Wrapping objects of a particular interface using dedicated wrapper objects of that interface is an established technique with many uses. The synchronized wrappers of the collection classes are probably the best known example, but there are many others. The SDK even has a special class which will generate wrapper objects at runtime: the
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.