5 Application Tuning

Using 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 once enabled, statement pooling is transparent to an application—use of the PreparedStatement object, including preparing and closing the statement, is no different.

You can 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.

Using 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.

For Statement objects, a batch typically consists of a set of INSERT or UPDATE statements. Statements that return result sets are not allowed in a batch. A SQL statement is added to a batch by calling the addBatch() method. The set of SQL statements associated with a batch are executed through the executeBatch() method.

For PreparedStatement objects, a 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 the Release 11.2.1:

• 256 for INSERT statements

• 31 for UPDATE statements

• 31 for DELETE 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), 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, see the Java Platform API specification for the Statement interface, particularly information about the executeBatch() method, at the following location:

http://java.sun.com/j2se/1.5.0/docs/api/index.html

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, 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, each time it is called. This is one of the costliest calls in JDBC.

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().

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 calls to ttXlaAcknowledge

Calls to the C XLA function ttXlaAcknowledge move the bookmark and involve updates to system tables, so one way to increase throughput is to wait until several transactions have been seen before issuing the call. This means that the reader application must have some tolerance for seeing the same set of records more than once. Moving the bookmark can be done manually using the Session CLIENT_ACKNOWLEDGE mode when instantiating a session:

Session session = connection.createSession (false, Session.CLIENT_ACKNOWLEDGE);

For many applications, setting this value to 100 is a reasonable choice.

Refer to "ttXlaAcknowledge" in Oracle TimesTen In-Memory Database C Developer's Guide for information about this function.

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 call back thread itself if they are using CLIENT_ACKNOWLEDGEMENT as acknowledgment mode. See "Receiving and processing updates".