I have an article for you to read
Improve JDBC Performance
Good coding principles, including using connection pools, adjusting fetch size of ResultSet objects, batching updates, using Unicode in databases, removing extra columns from SQL statements, and setting right transaction controls, can bring substantial performance gains
Java Database Connectivity (JDBC) API helps Java developers establish a connection with a database, send queries, update statements to the database, and process the results. The performance of JDBC application is affected by two factors: the performance of SQL statements and the performance of Java code. The performance of SQL is related to database modeling and SQL design, and database developer have more control over this factor. As a Java developer, you have more control over the use of JDBC API.
Choose the right driver
JDBC API is an interface implemented by JDBC drivers. The first thing to do in JDBC programming is picking the right driver.
There are four types of database drivers. Type I drivers, called JDBC-ODBC bridges, connect Java clients to relational databases via ODBC drivers, which are Microsoft-designed standard methods for accessing databases. ODBC binary code must be loaded on each client machine that uses this type of driver. The performance of Type I drivers is degraded because the call goes to database indirectly. Sun�s JDK includes a JDBC-ODBC bridge, which can be used for development but is unsuitable for production.
Type II drivers are native API/partly Java drivers that communicate with the database through binary database client code on client machines. They provide better performance than the type I drivers. However, since the client code is specific to a database and client platform, type II drivers are not suitable for Internet applications.
Type III drivers use a network protocol to communicate with middleware servers that mediate access to databases through database-client protocol, which is typically available only to database vendors.
Type IV drivers are pure Java drivers that access database directly through database-client protocol.
The rule of thumb is that Type I drivers are the slowest, while Type II, Type III, and Type IV drivers show faster performance. With the emergence of JIT and HotSpot virtual machines, pure Java drivers are becoming faster. For example, Oracle thin driver, which is a Type IV driver, is slightly faster than Type II OCI driver.
How to use JDBC API
Suppose you want to query a database and process the query result.
First, you want to open a Connection to the database:
String url = �jdbc
dbc:mydatasource�;
Connection con =
DriverManager.getConnection(url,
�username�, �password�);
The String, url, provides a way to specify the JDBC driver and data source.
Second, you want to create a JDBC Statement:
Statement stmt = con.createStatement();
Third, execute the Statement:
ResultSet rs = stmt.executeQuery(�SELECT
x, y FROM table1�);
Forth, process ResultSet object:
while (rs.next()) {
int x= rs.getInt(�x�);
int y= rs.getInt(�y�);
// � do something
}
ResultSet object contains rows of x and y. In order to access the x and y, we go to each row and retrieve the values according to their types and column names. The method next() moves a cursor to the next row each time. Since the cursor initially points to a position right before the first row, the first call moves cursor to the first row and so on, until the cursor is moved beyond the last row.
Fifth, clean up:
rs.close();
stmt.close();
con.close();
If you do not close these objects, they are automatically closed when they are garbage collected. However,
you should close them immediately for better performance.
Using connection pools
Connection objects are expensive to build. If connections are repeatedly opened and closed, performance will be slowed. This issue can be solved by caching Connection objects. A number of Connection objects are prepared in advance and placed in a pool. When a client opens a database connection, it gets one Connection Object from the pool. When the client closes a connection, it places Connection object back into the pool.
You can implement a connection pool by yourself. The good news is that most drivers support connection pools. The only thing you need to do is getting connection from DataSource instead of DriverManager. A DataSource object may be implemented in different ways, and most vendors provide ConnectionPoolDataSource objects that support connection pools. Normally, DataSource is accessed through JNDI service:
try {
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup
(�jdbc/myDataSource�);
Connection con = ds.getConnection
(�username�, �password�);
// � code using the pooled connection
} catch(SQLException e) {
// � code to handle exception
} finally {
if (con != null) {
con.close();
}
}
Note the code in finally block will guarantee that the connection is closed and returned to the connection pool.
Choose the right Statement
Statement, preparedStatement, and CallableStatement are different objects used to send commands to a database.
A Statement object is used to execute simple SQL statements with no parameters; a PreparedStatement object is used for precompiled SQL statements that may have IN parameters; and a CallableStatement object is used to execute SQL stored procedures, which are groups of SQL statements used to do certain tasks. CallableStatement may have IN, OUT, and INOUT parameters.
There is a misunderstanding that using a PreparedStatement is always faster than using a Statement object. In fact, this is not true. A PreparedStatement object is precompiled and stored for future use. If you want to execute a SQL command several times, you should use Statement because the time used in precompiling is too long and the benefit will show up only after you use a PreparedStatement for more than 50 ~ 60 times.
CallableStatement is used to handle stored procedures. The reason for using a stored procedure is that you want to execute a series of SQL commands in the same sequence every time. By putting them together, you save the time to move all the related data back and forth over the network. Also, a stored procedure can be compiled before it is used. To improve performance, can we define single Statement as a stored procedure and execute it as CallableStatement? The answer is no. There is some overhead in using stored procedure. If your procedure is very simple, the overhead will hurt the performance. You should use CallableStatement, when you have a complex task that requires several SQL statements to complete.
Batching updates:
Batching updates is good for performance because it allows Statement or PreparedStatement objects to submit multiple SQL updates at one time to a database. However, batching is not always supported by Statement objects. For example, Oracle thin and OCI driver only support batching when a PreparedStatement object is used. Note, if you use PreparedStatement, you need to batch at least 50 updates to gain the benefit of precompiling. If batching is supported by Statement objects, you don�t need to worry about precompiling time. You can code a batched Statement like this:
Statement stmt = con.createStatement();
stmt.addBatch(�INSERT INTO table1
VALUES (1, 2, 3, 4)�);
stmt.addBatch(�INSERT INTO table1
VALUES (2, 2, 3, 4)�);
stmt.addBatch(�INSERT INTO table1
VALUES (3, 2, 3, 4)�);
int [ ] results = stmt.executeBatch();
Note that since CallableStatement implements PreparedStatement interface, it also supports batching. However, you can only call stored procedures that have input parameters or no parameters at all.
CallableStatement cstmt =
con.prepareCall({call myProcedure(?, ?)});
cstmt.setInt(1, 2);
cstmt.setString(2, �Employee�);
cstmt.addBatch();
cstmt.setInt(1, 3);
cstmt.setString(2, �Employee�);
cstmt.addBatch();
int [ ] results = cstmt.executeBatch();
Processing ResultSet
Most of the time, we process SQL query results as ResultSet objects. A ResultSet object maintains a cursor, which points to a row in a database table. The cursor moves one row each time the method next() is called. You can process a ResultSet object like this:
Connection con =
DriverManager.getConnection(url);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery
(�SELECT x, y FROM table1�);
while (rs.next()) {
int x = rs.getInt(�x�);
int y = rs.getInt(�x�);
// � do something
}
In fact, if you know x column is the first column and y column is the second column, use column number. It is faster. You can try this:
while (rs.next()) {
int x= rs.getInt(1);
int y= rs.getInt(2);
// � do something
}
When we loop through the ResultSet object, a JDBC driver may go to a database several times depending on the size of ResultSet. By default, the JDBC driver will fetch some fixed number of rows every time. For example, if you are using Oracle thin driver to get a ResultSet of 50 rows, and the default fetch size is 10 for this driver, the driver will go to the database five times when you loop through the whole ResultSet object. However, if you set fetch size to 50, JDBC will only go to the database once. By adjusting fetch size, performance can be considerably improved.
Connection con =
DriverManager.getConnection(url);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(�SELECT
x, y FROM table1�);
while (rs.next()) {
int x= rs.getInt(1);
int y= rs.getInt(2);
// � do something
}
Selecting required columns only
Proper use of SQL improves performance. For example, if you have x, y, z, and w columns in a table called table1, and you want to query x and y columns, you do the following:
Connection con =
DriverManager.getConnection(url);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(�SELECT
* FROM table1�);
while (rs.next()) {
int x= rs.getInt(1);
int y= rs.getInt(2);
// � do something
}
In this case, the JDBC driver will make unnecessary database query for those columns including z and w. It hurts performance. In addition, building a ResultSet object holding two more columns also hurts performance.
Using Unicode
Java process strings in Unicode and therefore, if you store strings in a database in Unicode, the JDBC driver will use Unicode strings directly without conversion. If you store strings in non-Unicode format, the JDBC driver will do the conversion, which slows the performance.
You should know Unicode requires two bytes per character instead of one byte and this could be a significant consumption of memory space in your database. Also, other non-Java applications may not like Unicode.
Transaction and performance
A transaction is a group of statements that are executed as a
unit. Either all of the statements are executed, or none of them are executed. By default, JDBC connection executes each individual SQL statement as a transaction, which means each statement is automatically committed as it is executed. So if you put several statements together in one transaction, the amount of time used to execute commit methods will be saved. If auto-commit mode is set to false, a transaction will not commit until the method commit is called explicitly. The beginning of a transaction starts after disabling the auto-commit mode or after calling the methods commit or rollback.
Connection con =
DriverManager.getConnection(url);
con.setAutoCommit(false);
PreparedStatement update1 =
con.prepareStatement(�UPDATE table1
SET x = ? WHERE y = ?�);
update1.setInt(1,10);
update1.setInt(2,11);
update1.executeUpdate();
PreparedStatement update2 =
con.prepareStatement(�UPDATE table1
SET z = ? WHERE w = ?�);
update2.setInt(1,10);
update2.setInt(2,11);
update2.executeUpdate();
connection.commit();
connection.setAutoCommit(true);
Using this coding technique, you execute the commit method once rather than twice.
Transaction isolation level manages the interaction among transactions that access a database at the same time. Due to the decreased concurrency among transactions, the higher the level of isolation, the slower the application executes. The default isolation level of connection depends on the driver. Normally, a JDBC driver takes isolation level of the database as its default isolation level. The method con.setTransactionIsolation sets the isolation level of a connection. The isolation levels from the lowest to the highest are read uncommitted, read committed, repeatable read, and serializable.
Isolation level controls three types of inconsistencies including dirty reads, non-repeatable reads, and phantoms:
Dirty reads: One transaction A has changed a row in the table, but hasn't committed yet. Transaction B reads it, but the data could in fact be non-existent because B may still roll back later.
Non-repeatable reads: Transaction A performs a read, but B modifies or deletes it during its transaction. If A reads the same row again, it will get no or different results.
Phantoms: Transaction A does a query on a set of rows to perform an operation. B modifies the table such that the query of A would have given a different result. The table may be left inconsistent.
Read uncommitted prevents none of the above inconsistencies; read committed prevents dirty reads; repeatable read prevents Dirty reads and non-repeatable reads; and serializable prevents all of the above inconsistencies. Setting the lowest acceptable isolation level improves JDBC performance.
Conclusion
The performance of JDBC code can be improved by using connection pools, removing extra columns from SQL statement, adjusting fetch size of ResultSet object, batching updates, using Unicode in database, and using the right transaction control.
The coding tips mentioned in this article are useful things to learn but they are not silver bullets for all performance problems. You should consider performance at the beginning of a project and be aware that architectural design affects performance profoundly and coding tips are secondary to it.
About the author
Jun Hong is a senior Java developer working at UBS Warburg. He is a Sun certified Java programmer, developer, and
J2EE architect. Also, he has a Master�s Degree in computer science.
Resources
JDBC API tutorial and Reference by Seth White, Maydene Fisher, Rick Cattell, Graham Hamilton, Mark Hapner (Addison Wesley Longman, 2000)
Java Programming with Oracle JDBC by Donald K. Bales and Don Bales (O�Reilly, 2001)
