> ### Managing Connections and Updates

> The guidelines in this section will help you to manage connections and updates to improve system performance for your ODBC applications.

> #### Managing Connections

> Connection management affects application performance. Optimize your applications by connecting once and using multiple statement handles, instead of performing multiple connections. Avoid connecting to a data source after establishing an initial connection.

> Although gathering driver information at connection is a good practice, it often is more efficient to gather it in one step rather than two steps. Some ODBC applications are designed to call informational gathering routines that have no record of already attached connection handles. For example, some applications establish a connection and then call a routine in a separate DLL or shared library that reattaches and gathers information about the driver. Applications that are designed as separate entities should pass the already connected HDBC pointer to the data collection routine instead of establishing a second connection.

> Another bad practice is to connect and disconnect several times throughout your application to perform SQL statements. Connection handles can have multiple statement handles associated with them. Statement handles can provide memory storage for information about SQL statements. Therefore, applications do not need to allocate new connection handles to process SQL statements. Instead, applications should use statement handles to manage multiple SQL statements.

> On Windows, you can significantly improve performance with connection pooling, especially for applications that connect over a network or through the World Wide Web. With connection pooling, closing connections does not close the physical connection to the database. When an application requests a connection, an active connection from the connection pool is reused, avoiding the network input/output needed to create a new connection.

> Connection and statement handling should be addressed before implementation. Spending time and thoughtfully handling connection management improves application performance and maintainability.

> #### Managing Commits in Transactions

> Committing data is extremely disk input/output intensive and slow. If the ODBC driver can support transactions, always turn Autocommit off.

> What does a commit actually involve? The database server must flush back to disk every data page containing updated or new data. This is not a sequential write, but a searched write to replace existing data in the table. By default, Autocommit is on when connecting to a data source, and Autocommit mode usually impairs performance because of the significant amount of disk input/output required to commit every operation.

> Some database servers do not provide an Autocommit mode. For this type of server, the ODBC driver must explicitly issue a COMMIT statement and a BEGIN TRANSACTION for every operation sent to the server. In addition to the large amount of disk input/output required to support Autocommit mode, a performance penalty is paid for up to three network requests for every statement issued by an application.

> #### Choosing the Right Transaction Model

> Many systems support distributed transactions; that is, transactions that span multiple connections. Distributed transactions are at least four times slower than normal transactions due to the logging and network input/output necessary to communicate between all the components involved in the distributed transaction. Unless distributed transactions are required, avoid using them. Instead, use local transactions when possible.

> #### Using Positional Updates and Deletes

> Use positional updates and deletes or SQLSetPos to update data. Although positional updates do not apply to all types of applications, developers should use positional updates and deletes when it makes sense. Positional updates (using "update where current of cursor" or using SQLSetPos) allow the developer to signal the ODBC driver to "change the data here" by positioning the database cursor to the appropriate row to be changed. The designer is not forced to build a complex SQL statement and simply supplies the data that will be changed.

> In addition to making the application more easily maintainable, positional updates usually result in improved performance. Because the database server is already positioned on the row for the Select statement in process, performance-expensive operations to locate the row to be changed are not needed. If the row must be located, the server usually has an internal pointer to the row available (for example, ROWID).

> #### Using SQLSpecialColumns

> Use SQLSpecialColumns to determine the optimal set of columns to use in the Where clause for updating data. Often, pseudo-columns provide the fastest access to the data, and these columns can only be determined by using SQLSpecialColumns.

> Some applications cannot be designed to take advantage of positional updates and deletes. These applications typically update data by forming a Where clause consisting of some subset of the column values returned in the result set. Some applications may formulate the Where clause by using all searchable result columns or by calling SQLStatistics to find columns that may be part of a unique index. These methods usually work, but may result in fairly complex queries.

> Consider the following:

>     rc = SQLExecDirect (hstmt, "SELECT first_name, last_name,
>        ssn, address, city, state, zip FROM emp", SQL_NTS);
>     // fetchdata
>     ...
>     rc = SQLExecDirect (hstmt, "UPDATE EMP SET ADDRESS = ?
>        WHERE first_name = ? and last_name = ? and ssn = ?
>        and address = ? and city = ? and state = ? and zip = ?",
>        SQL_NTS);
>     // fairly complex query

> Applications should call SQLSpecialColumns/SQL_BEST_ROWID to retrieve the optimal set of columns (possibly a pseudo-column) that identifies a specific record. Many databases support special columns that are not explicitly defined by the user in the table definition but are "hidden" columns of every table (for example, ROWID and TID). These pseudo-columns provide the fastest access to data because they typically point to the exact location of the record. Because pseudo-columns are not part of the explicit table definition, they are not returned from SQLColumns. To determine if pseudo-columns exist, call SQLSpecialColumns.

> Consider the previous example again:

>     ...
>     rc = SQLSpecialColumns (hstmt, ..... 'emp', ...);
>     ...
>     rc = SQLExecDirect (hstmt, "SELECT first_name, last_name,
>        ssn, address, city, state, zip, ROWID FROM emp",
>        SQL_NTS);
>     // fetch data and probably "hide" ROWID from the user
>     ...
>     rc = SQLExecDirect (hstmt, "UPDATE emp SET address = ?
>        WHERE ROWID = ?", SQL_NTS);
>     // fastest access to the data!

> If your data source does not contain special pseudo-columns, the result set of SQLSpecialColumns consists of the columns of the optimal unique index on the specified table (if a unique index exists); therefore, your application does not need to call SQLStatistics to find the smallest unique index.