Databases Reference
In-Depth Information
Performance Tip
To reduce the number of network round trips when updating large
amounts of data, you can send multiple
Insert
statements to the data-
base at a time using the
SQLSetStmtAttr
function with the following
arguments:
SQL_ATTR_PARAMSET_SIZE
sets the array size of the parame-
ter,
SQL_ATTR_PARAMS_PROCESSED_PTR
assigns a variable filled by
SQLExecute
(containing the number of rows that are inserted), and
SQL_ATTR_PARAM_STATUS_PTR
points to an array in which status informa-
tion for each row of parameter values is retrieved.
With ODBC 3.
x
, calls to
SQLSetStmtAttr
with the
SQL_ATTR_
PARAMSET_SIZE
,
SQL_ATTR_PARAMS_PROCESSED_PTR
, and
SQL_ATTR_PARAM_
STATUS_PTR
arguments supersede the ODBC 2.
x
call to
SQLParamOptions
.
Before executing the statement, the application sets the value of each data
element in the bound array. When the statement is executed, the driver tries to
process the entire array contents using one network round trip. For example, let's
compare the following examples.
Example A: Executing a Prepared Statement Multiple Times
A prepared statement is used to execute an
Insert
statement multiple
times, requiring 101 network round trips to perform 100
Insert
opera-
tions: 1 round trip to prepare the statement and 100 additional round
trips to execute its iterations.
rc = SQLPrepare (hstmt, "INSERT INTO DailyLedger (...)" +
"VALUES (?,?,...)", SQL_NTS);
// bind parameters
...
do {
// read ledger values into bound parameter buffers
...
rc = SQLExecute (hstmt);
// insert row
} while ! (eof);
