Updates and Transactions

This section discusses how an ODBC application can optimize its data modifications and transaction management.

SQLRowCount

If an ODBC application needs to know how many rows were affected by a data modification (UPDATE, INSERT, DELETE), it can call the SQLRowCount function after the modification completes. SQLRowCount is generally not filled after a SELECT statement, although it may be if the application is using server cursors. For more information, see Microsoft SQL Server Programming ODBC for Microsoft SQL Server.

Batching Procedure Calls

SQLParamOptions can be used to efficiently call a stored procedure multiple times with different parameters. SQLBindParameter normally binds a single variable to a parameter, and SQLParamOptions is used to extend this binding so that it binds an array of variables to a parameter.

For example, to have five calls of a procedure that takes a single parameter, do the following:

1. Allocate an array of five variables.
   
   
2. Use SQLBindParameter to bind the parameter to the lead element of the array.
   
   
3. Use SQLParamOptions to tell the driver that the parameter is bound to an array with five elements.

When you issue SQLExecDirect, the driver builds a single batch calling the procedure five times, with a different element from the array associated with each procedure call. This is more efficient than sending five separate batches to the server.

This process also works with procedures that take multiple parameters. Allocate an array for each parameter with the same number of elements in each array, then call SQLParamOptions specifying the number of elements.

Autocommit vs. ANSI Transaction Management

ODBC has two ways in which applications manage transactions. The application controls the autocommit mode by calling:

SQL_AUTOCOMMIT_ON
SQLSetConnectOption(hdbc,
                    SQL_AUTOCOMMIT,
                    SQL_AUTOCOMMIT_ON);

When autocommit is on, each statement is a separate transaction and is automatically committed when it completes successfully.

SQL_AUTOCOMMIT_OFF
SQLSetConnectOption(hdbc,
                    SQL_AUTOCOMMIT,
                    SQL_AUTOCOMMIT_OFF);

When autocommit is turned off, the next statement sent to the database starts a transaction. The transaction remains in effect until the application calls SQLTransact with either the SQL_COMMIT or SQL_ROLLBACK options. The statement sent to the database after SQLTransact starts the next transaction.

ODBC applications should not mix managing transactions through the ODBC autocommit options with calling the Transact-SQL transaction statements. If an application does this, it could generate undetermined results. The application should manage transactions in one of the following ways:

• Use SQLSetConnectOption to set the ODBC autocommit modes.
  
  
• Use Transact-SQL statements, such as BEGIN TRANSACTION. (The SQLSetConnectOption should be left at its default setting of autocommit on.)

Applications should keep transactions as short as possible by not requiring user input while in a transaction. User input can take a long time, and all that time, the application is holding locks that may adversely impact other tasks needing the same data.

An application should do all required queries and user interaction needed to determine the scope of the updates before starting the transaction. The application should then begin the transaction, do the updates, and immediately commit or rollback the transaction without user interaction.

Using Transactions to Optimize Logging

Applications doing several data modifications (INSERT, UPDATE, or DELETE) at one time should do these within one transaction (autocommit off). When autocommit is on, each individual statement is committed by the server. Commits cause the server to flush out the modified log records. To improve performance, do all updates within one transaction and issue a single commit when all the changes have been made. Care must be taken to not include too many updates within one transaction, however. Performing many updates causes the transaction to be open longer and more pages to be locked with exclusive locks, which increases the probability that other users will be blocked by the transaction. Grouping modifications into a single transaction must be done in a way that balances multiuser concurrency with single-user performance.

For applications that do not require a high degree of data accuracy, consider using the SQL_TXN_READ_UNCOMMITED transaction isolation level to minimize the locking overhead on the server.