Use SQLExtendedFetch instead of SQLFetch to retrieve data. The ODBC call load decreases (resulting in better performance) and the code is less complex, resulting in more maintainable code.
Most ODBC drivers now support SQLExtendedFetch for forward only cursors; yet, most ODBC applications use SQLFetch to retrieve data. Again, consider the previous example using SQLExtendedFetch, instead of SQLFetch:
// call SQLBindCol 1 time specifying row-wise binding
do {
rc = SQLExtendedFetch (hstmt, SQL_FETCH_NEXT, 0,
&RowsFetched, RowStatus);
} while ((rc == SQL_SUCCESS) || (rc==SQL_SUCCESS_WITH_INFO));
Notice the improvement from the previous examples. The initial call load was more than 1890 ODBC calls. By choosing ODBC calls carefully, the number of ODBC calls made by the application has now been reduced to 4 (1 SQLSetStmtOption + 1 SQLExecDirect + 1 SQLBindCol + 1 SQLExtendedFetch). In addition to reducing the call load, many ODBC drivers retrieve data from the server in arrays, further improving performance by reducing network traffic.
For ODBC drivers that do not support SQLExtendedFetch, the application can enable forward-only cursors using the ODBC cursor library (call SQLSetConnectOption using SQL_ODBC_CURSORS/ SQL_CUR_USE_IF_NEEDED). Although using the cursor library does not improve performance, it should not be detrimental to application response time when using forward-only cursors (no logging is required). Furthermore, using the cursor library when SQLExtendedFetch is not supported natively by the ODBC driver simplifies the code because the application can depend on SQLExtendedFetch being available. The application does not require two algorithms (one using SQLExtendedFetch and another using SQLFetch).
