7 Application Tuning

Bypass driver manager if appropriate

TimesTen permits ODBC applications that do not need some of the functionality provided by the driver manager to link without it. In particular, applications that do not need ODBC access to database systems other than TimesTen should consider omitting the driver manager. This is done by linking the application directly with the TimesTen Data Manager or client driver, as described in "Linking options"3. The performance improvement will be approximately 20 percent.

"Testing link options" explains how to determine whether an application is linked directly with the driver or with the driver manager.

Using arrays of parameters for batch execution

You can improve performance by using groups, referred to as "batches", of statement executions in your application.

The SQLParamOptions ODBC function allows an application to specify multiple values for the set of parameters assigned by SQLBindParameter. This is useful for processing the same SQL statement multiple times with various parameter values. For example, your application can specify multiple sets of values for the set of parameters associated with an INSERT statement, and then execute the INSERT statement once to perform all the insert operations.

TimesTen supports the use of SQLParamOptions with INSERT, UPDATE and DELETE statements, but not with SELECT statements. TimesTen recommends the following batch sizes for Release 11.2.1:

• 256 for INSERT statements

• 31 for UPDATE statements

• 31 for DELETE statements

Table 7-1 provides a summary of SQLParamOptions arguments. Refer to ODBC API reference documentation for details.

Assuming the crow value is greater than 1, the rgbValue argument of SQLBindParameter points to an array of parameter values and the pcbValue argument points to an array of lengths. (Also see "SQLBindParameter function".)

Refer to the TimesTen Quick Start demo source file bulkinsert.c for a complete working example of batching. (Also, for programming in C++ with TTClasses, see bulktest.cpp.)

Avoid excessive binds

The purpose of a SQLBindCol or SQLBindParameter call is to associate a type conversion and program buffer with a data column or parameter. For a given SQL statement, if the type conversion or memory buffer for a given data column or parameter is not going to change over repeated executions of the statement, it is better not to make repeated calls to SQLBindCol or SQLBindParameter.

Avoid SQLGetData

SQLGetData can be used for fetching data without binding columns. This can sometimes have a negative impact on performance because applications have to issue a SQLGetData ODBC call for every column of every row that is fetched. In contrast, using bound columns requires only one ODBC call for each fetched column. Further, the TimesTen ODBC driver is more highly optimized for the bound columns method of fetching data.

SQLGetData can be very useful, though, for doing piece-wise fetches of data from long character or binary columns.

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. To avoid data type conversions:

• Match input argument types to expression types.

• Match the types of output buffers to the types of the fetched values.

• Match the connection character set to the database character set.

Bulk fetch rows of TimesTen data

TimesTen provides the TT_PREFETCH_COUNT option, which can be set through SQLSetStmtOption and allows an application to fetch multiple rows of data. This feature is available for applications that use the READ_COMMITTED isolation level. For applications that retrieve large amounts of TimesTen data, fetching multiple rows can increase performance greatly. However, locks are held on all rows being retrieved until all the application has received all the data, decreasing concurrency. For more information on how to use TT_PREFETCH_COUNT, see "Prefetching multiple rows of data".