Use 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 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 SQLParamOptions with INSERT, UPDATE, DELETE, and MERGE statements, but not with SELECT statements. (TimesTen Scaleout does not support MERGE statements.)
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.
Table 7-1 provides a summary of SQLParamOptions arguments. Refer to ODBC API reference documentation for details.
Table 7-1 SQLParamOptions Arguments
| Argument | Type | Description |
|---|---|---|
|
|
|
Statement handle |
|
|
|
Number of values for each parameter |
|
|
|
Pointer to storage for the current row number |
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.)
In the TimesTen Classic Quick Start, refer to source file bulkinsert.c for a complete working example of batching. (Also, for programming in C++ with TTClasses, see bulktest.cpp.) See About TimesTen Quick Start and Sample Applications.
Note:
When using SQLParamOptions with the TimesTen client/server driver, data-at-execution parameters are not supported. (An application can pass the value for a parameter either in the SQLBindParameter rgbValue buffer or with one or more calls to SQLPutData. Parameters whose data is passed with SQLPutData are known as data-at-execution parameters. These are commonly used to send data for SQL_LONGVARBINARY and SQL_LONGVARCHAR parameters and can be mixed with other parameters.)