JDBC Performance Tuning

1.0  Introduction
        1.1  Connection Pooling
2.0  Auto-Commit
3.0  Closing JDBC objects
4.0  Optimization with Statement
	4.1 Choose right Statement interface
        4.2  Do batch update
        4.3  Do batch retrieval using Statement
        4.4  Close Statement when finished
5.0  Optimization with ResultSet
	5.1  Do batch retrieval using ResultSet
        5.2  Setup proper direction of processing rows
        5.3  Use proper getxxx() methods
        5.4  Close ResultSet when finished
6.0  Optimization with SQL Query 

1.0 Introduction

Performance is usually considered an issue at the end of a development cycle when it should really be considered from the start. Often, a task called "performance tuning" is done after the coding is complete, and the end user of a program complains about how long it takes the program to complete a particular task.

Java Database Connectivity (JDBC) is used extensively by many Java applications. Getting database connection is very expensive. If your application requires database connections that are repeatedly opened and closed, this can become a significant performance issue. Now let us see how best we can optimize by clearing the bottlenecks and increase the performance.

When the application server accesses the database, it is also one of the bottlenecks to be considered in the performance.

I/O operations (keyboard input) on the system will slower the connection as I/O threads have maximum priority.

Whenever all the database records are to be returned, better way is put all the records in a Vector and return.

The following example 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 into a String[] array and finally 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 = new String[numColumns];
    Vector allRows = new Vector();
    while(resultSet.next())
    {                     //ResultSet access is 1-based, arrays are 0-based
      aRow[i] = resultSet.getString(i+1);
    } 
    allRows.addElement(aRow); 
    return allRows;

Now start reading JDBC Performance Tuning and pass your comments at the end of this tutorial.

Adding to an array is more efficient than adding each element to a vector.

1.1 Connection Pooling

When WebLogic Application Server (or any Web server like Tomcat) starts, connections from the connection pools are opened and are available to all clients. When a client closes a connection from a connection pool, the connection is returned to the pool and becomes available for other clients; the connection itself is not closed. There is little cost to opening and closing pool connections.

How many connections should you create in the pool? A connection pool can grow and shrink according to configured parameters, between a minimum and a maximum number of connections. The best JDBC Performance Tuning will always be when the connection pool has as many connections as there are concurrent users.

2.0 Auto-Commit

If auto-commit feature is on, every SQL statement is committed as it is executed. If more than one SQL statement is executed by your program, then a small performance increase can be achieved by turning off auto-commit and by committing all transactions at a time.

try
{
  connection.setAutoCommit(false); 
  PreparedStatement ps = connection.preareStatement( "UPDATE employee SET Address=? WHERE name=?");
  ps.setString(1,"Austin");
  ps.setString(2,"RR");
  ps.executeUpdate();
  PreparedStatement ps1 = connection.prepareStatement( "UPDATE account SET salary=? WHERE name=?");
  ps1.setDouble(1, 5000.00);
  ps1.setString(2,"RR");
  ps1.executeUpdate();
  connection.commit();
  connection.setAutoCommit(true);
}
catch(SQLException e)
{ 
  connection.rollback();
}
finally
{
  if(ps != null)  { ps.close(); }
  if(ps1 != null) { ps1.close(); }
  if(connection != null) { connection.close(); }
}

This batch transaction gives good JDBC performance by reducing commit calls after each statement’s execution.

3.0 Explicitly closing JDBC objects

Oracle JDBC objects such as Connection, ResultSet, PreparedStatement and CallableStatement do not have finalizer methods. If you do not explicitly call the close() method for each of these objects as soon as you are done wit them, your programs are likely to run out of resources.

Close JDBC objects in finally block. In addition of closing, assign null value afterwards, so that it will be immediately eligible for garbage collection. Doing these things will keep your programs nice and tidy.

Pages: 1 2