This chapter describes how to tune an ODBC application to run optimally on a TimesTen database. See "TimesTen Database Performance Tuning" in Oracle TimesTen In-Memory Database Operations Guide for more general tuning tips.
This chapter includes the following topics:
TimesTen permits ODBC applications that do not need some of the functionality provided by a driver manager to link without one. In particular, applications that do not need ODBC access to database systems other than TimesTen should omit the driver manager. This is done by linking the application directly with the TimesTen direct or client driver, as described in "Linking options". The performance improvement is significant.
Note:It is permissible for some applications connected to a database to be linked with the driver manager, while others connected to the same database are direct-linked.
You can improve performance by using groups, referred to as batches, of statement executions in your application.
SQLParamOptions ODBC function enables 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
MERGE statements, but not with
SELECT statements. TimesTen recommends the following batch sizes for TimesTen 11g Release 2 (11.2.2):
Table 7-1 provides a summary of
SQLParamOptions arguments. Refer to ODBC API reference documentation for details.
Number of values for each parameter
Pointer to storage for the current row number
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
SQLParamOptionswith the TimesTen Client/Server driver, data-at-execution parameters are not supported. (An application can pass the value for a parameter either in the
rgbValuebuffer or with one or more calls to
SQLPutData. Parameters whose data is passed with
SQLPutDataare known as data-at-execution parameters. These are commonly used to send data for
SQL_LONGVARCHARparameters and can be mixed with other parameters.)
The purpose of a
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
SQLBindParameter. Simply prepare once and bind once to execute many times.
Note:A call to
SQL_UNBINDoption unbinds all columns.
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 piecewise fetches of data from long character or binary columns. (This is discussed with respect to LOBs in "Using the LOB piecewise data interface in ODBC".)
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.
TimesTen provides the
TT_PREFETCH_COUNT option, which can be set through
SQLSetStmtOption and enables 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 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".