Tuning JDBC Applications

Use Prepared Statement Pooling

TimesTen supports prepared statement pooling for pooled connections 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 are pooled at the time of creation if the pool has not reached its capacity. 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 is removed from the pool.

Tip:

With prepared statement pooling, JDBC considers two statements to be identical if their SQL (including comments) is identical, regardless of other considerations such as optimizer settings. Do not use prepared statement pooling in a scenario where different optimizer hints may be applied to statements that are otherwise identical. In this scenario, a statement execution may result in the use of an identical statement from the pool with an unpredictable optimizer setting.

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. (TimesTen Scaleout does not support 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.

The ideal batch size for any of these database operations varies according to details of the user environment and requires testing and experimentation to determine.

Tip:

In TimesTen Classic, it is important to use a batch size that is an exact multiple of 256 for inserts, in order to optimize the insert mechanism.

Here is an example of batching 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 ();

The following example batches prepared statements:

// 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 the examples 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 are attempted.

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

https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html

Note:

Associative array parameters are not supported with JDBC batch execution. (See Working with Associative Arrays.)

Bulk Fetch Rows of TimesTen Data

TimesTen provides an extension that enables an application to fetch multiple rows of data.

For applications that retrieve large amounts of 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. 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.

For example:

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

Optimize Queries

TimesTen provides the TimesTenConnection method setTtPrefetchClose() to optimize query performance with a true setting.

Refer to Optimizing Query Performance.