5 Java Application Tuning

Use prepared statement pooling

TimesTen supports prepared statement pooling for pooled connections, as discussed in the JDBC 3.0 specification, through the TimesTen ObservableConnectionDS class. This is the TimesTen implementation of ConnectionPoolDataSource. Note that statement pooling is transparent to an application. Use of the PreparedStatement object, including preparing and closing the statement, is no different.

Enable prepared statement pooling and specify the maximum number of statements in the pool by calling the ObservableConnectionDS method setMaxStatements(). A value of 0, the default, disables prepared statement pooling. Any integer value greater than 0 enables prepared statement pooling with the value taken as the maximum number of statements. Once set, this value should not be changed.

Prepared statements or callable statements will be pooled at the time of creation if the pool has not reached its capacity. In Java 6, you can remove a prepared statement or callable statement from the pool by calling setPoolable(false) on the statement object. After the statement is closed, it will be removed from the pool.

Use arrays of parameters for batch execution

You can improve performance by using groups, referred to as batches, of statement executions, calling the addBatch() and executeBatch() methods for Statement or PreparedStatement objects.

A batch can consist of a set of INSERT, UPDATE, DELETE, or MERGE statements. Statements that return result sets, such as SELECT statements, are not allowed in a batch. A SQL statement is added to a batch by calling addBatch() on the statement object. The set of SQL statements associated with a batch are executed through the executeBatch() method.

For PreparedStatement objects, the batch consists of repeated executions of a statement using different input parameter values. For each set of input values, create the batch by using appropriate setXXX() calls followed by the addBatch() call. The batch is executed by the executeBatch() method.

TimesTen recommends the following batch sizes for TimesTen 11g Release 2 (11.2.2):

• 256 for INSERT statements

• 31 for UPDATE statements

• 31 for DELETE statements

• 31 for MERGE statements

// turn off autocommit
conn.setAutoCommit(false);

Statement stmt = conn.createStatement();
stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, 260)");

// submit a batch of update commands for execution
int[] updateCounts = stmt.executeBatch();
conn.commit ();

// turn off autocommit
conn.setAutoCommit(false);
// prepare the statement
PreparedStatement stmt = conn.prepareStatement
                         ("INSERT INTO employees VALUES (?, ?)");

// first set of parameters
stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
stmt.addBatch();

// second set of parameters
stmt.setInt(1, 3000);
stmt.setString(2, "Bill Barnes");
stmt.addBatch();

// submit the batch for execution. Check update counts
int[] updateCounts = stmt.executeBatch();
conn.commit ();

For either a Statement or PreparedStatement object, the executeBatch() method returns an array of update counts (updateCounts[] in Example 5-1 and Example 5-2 above), with one element in the array for each statement execution. The value of each element can be any of the following:

• A number indicating how many rows in the database were affected by the corresponding statement execution

• SUCCESS_NO_INFO, indicating the corresponding statement execution was successful, but the number of affected rows is unknown

• EXECUTE_FAILED, indicating the corresponding statement execution failed

  Once there is a statement execution with EXECUTE_FAILED status, no further statement executions will be attempted.

For more information about using the JDBC batch update facility, refer to the Javadoc for the java.sql.Statement interface, particularly information about the executeBatch() method, at the following locations (the first for Java 6, the second for Java 5):

http://docs.oracle.com/javase/6/docs/api/
http://docs.oracle.com/javase/1.5.0/docs/api/

Bulk fetch rows of TimesTen data

TimesTen provides an extension that allows an application to fetch multiple rows of data. For applications that retrieve large amounts of TimesTen data, fetching multiple rows can increase performance greatly. However, when using Read Committed isolation level, locks are held on all rows being retrieved until the application has received all the data, decreasing concurrency. For more information on this feature, see "Fetching multiple rows of data".

Use the ResultSet method getString() sparingly

Because Java strings are immutable, the ResultSet method getString() must allocate space for a new string in addition to translating the underlying C string to a Unicode string, making it a costly call.

In addition, you should not call getString() on primitive numeric types, like byte or int, unless it is absolutely necessary. It is much faster to call getInt() on an integer column, for example.

Avoid data type conversions

TimesTen instruction paths are so short that even small delays due to data conversion can cause a relatively large percentage increase in transaction time.

Use the appropriate getXXX() method on a ResultSet object for the data type of the data in the underlying database. For example, if the data type of the data is DOUBLE, to avoid data conversion in the JDBC driver you should call getDouble(). Similarly, use the appropriate setXXX() method on the PreparedStatement object for the input parameter in an SQL statement. For example, if you are inserting data into a CHAR column using a PreparedStatement, you should use setString().

Close connections, statements, and result sets

For better performance, always close JDBC objects such as connection, statement, and result set instances when finished using them. Example 5-3 shows typical usage.

Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      // create connections, execute statements, etc.
      // Handle any errors
    } catch (SQLException ex) {
      // See "Handling errors".
    }
    finally {
      // Close JDBC objects such as connections, statements, result sets, etc.
      if (rs != null) {
        try {
          rs.close();
        }
        catch(SQLException finalex) {
          // See "Handling errors".
        }        
      }
      if (stmt != null) {
        try {
          stmt.close();
        }
        catch(SQLException finalex) {
          // See "Handling errors".
        }
      }
      // Always, close the connection to TimesTen
      if (conn != null) {
        try {
          conn.close();
        }
        catch(SQLException finalex) {
       // See "Handling errors".
        }
      }

Configure xlaPrefetch parameter

The code underlying the JMS layer that reads the transaction log is more efficient if it can fetch as many rows as possible before presenting the object/rows to the user. The amount of prefetching is controlled in the jmsxla.xml configuration file with the xlaPrefetch parameter. Set the prefetch count to a large value like 100 or 1000.

Reduce frequency of update acknowledgments

In JMS/XLA, acknowledging updates moves the bookmark and results in updates to system tables. You can typically improve application performance by waiting until several updates have been detected before issuing the acknowledgment. You can control the acknowledgment frequency in either of the following modes. (See "XLA acknowledgment modes" for related information.)

• DUPS_OK_ACKNOWLEDGE, where JMS/XLA prefetches records according to the xlaprefetch setting, and an acknowledgment is automatically sent when the last record in the prefetched block is read.

• CLIENT_ACKNOWLEDGE, where you manually call the acknowledge() method on the MapMessage instance as desired.

The appropriate choice for acknowledgment frequency depends on your application logic. Acknowledging after every 100 updates, for example, has been used successfully. Be aware, however, that there is a tradeoff. Acknowledgments affect XLA log holds, and acknowledging too infrequently may result in undesirable log file accumulation. (Also see "XLA bookmarks and transaction log holds".)

Handling high event rates

The synchronous interface is suitable only for applications with low event rates and for which AUTO_ACKNOWLEDGE or DUPS_OK_ACKNOWLEDGE acknowledgment modes are acceptable. Applications that require CLIENT_ACKNOWLEDGE acknowledgment mode and applications with high event rates should use the asynchronous interface for receiving updates. They should acknowledge the messages on the callback thread itself if they are using CLIENT_ACKNOWLEDGE as acknowledgment mode. See "Receiving and processing updates".