Performance, General

•  SQLExecDirect—Using SQLPrepare and SQLExecute to prepare the SQL state-ment may not get significantly better performance if the SQL being executed is performed against the system with any frequency, which is the case in many moderate-to-heavily used online applications. This is due to Oracle’s caching of SQL and reuse of the prepared SQL statement.

•  Set the SQL_ATTR_NOSCAN attribute of the ODBC SQLSetConnectAttr function or the ODBC SQLSetStmtAttr function to TRUE if you are not using ODBC escape clauses.

•  Use the ODBC SQLBindCol function, instead of the ODBC SQLGetData function, to retrieve values from SELECT statements. Although both will retrieve data into variables, the SQLBindCol binds your variable directly to the values returned from the SELECT statement, whereas the SQLGetData function grabs the values from a temporary result set after the query returns the values. SQLGetData will be slower due to the extra function call and the dynamic nature of its data retrieval.

Update Performance

•  Set the SQL_ATTR_AUTOCOMMIT attribute of the ODBC SQLSetConnectAttr function to OFF, and perform manual COMMIT or ROLLBACK operations using the ODBC SQLEndTran function.

•  Although SELECT statements are never blocked by UPDATE statements, the Oracle server will wait indefinitely for lock conflicts between two update transactions. You can limit the amount of time that the Oracle server will wait for locks to be resolved by calling the ODBC SQLSetConnectAttr function before connecting to the data source. Specify a nonzero value for the SQL_ATTR_QUERY_TIMEOUT attribute in the ODBC SQLSetStmtAttr function.

OR

•  You can set the Oracle8 ODBC Driver’s LockTimeOut entry in the oraodbc.ini file. The value you enter for the LockTimeOut parameter is the number of seconds after which an Oracle server will time out if it cannot obtain the requested locks. In the following example, the Oracle server will time out after 60 seconds:

[Oracle ODBC Driver Common] 

Driver Logging=0 

RemoveControlChars=NO 

LockTimeOut=60 

Select Performance

•  If you are only reading data (only SELECT statements), you can specify Connect to Database in Read-Only Mode when you are setting up the ODBC data source in the ODBC Administrator, or set the SQL_ATTR_ACCESS_MODE attribute of the ODBC SQLSetConnectAttr function to SQL_MODE_READ_ONLY.

•  For large result sets, use the ODBC SQLFetchScroll function instead of the ODBC SQLFetch function due to SQLFetchScroll’s capability to view the result set before it has been completely retrieved from the database.

•  Set the Prefetch Count for Database Options in the Oracle8 ODBC Setup dialog box to a value greater than 1. Usually 25 to 100 is a good range for applications displaying a list of rows from the database on a screen. Increasing this value instructs the server to return multiple rows in a single fetch operation to the client’s cache, thus improving performance for applications designed to return groups of adjacent rows to users. Choose a value that works best for your application.

Troubleshooting via Logging

You can enable Oracle8 ODBC driver to identify problems with applications via its logging function. When logging is enabled, log files are written to the current working directory. It should be noted that log files can become quite large and slow down performance. Therefore, enable logging only when necessary, and disable it when it is no longer needed.

Installing the Oracle8 ODBC driver creates the following section in the oraodbc.ini file:

[Oracle ODBC Driver Common] 

Driver Logging=0 

RemoveControlChars=NO 

LockTimeOut=0 

Proxy Access=0 

A value of 0 for logging indicates that Oracle8 ODBC driver logging is turned off.

To enable Oracle8 ODBC Driver logging, you need to insert a valid Oracle8 ODBC driver logging value plus 1 for the Driver Logging entry:

[Oracle ODBC Driver Common] 

Driver Logging=7 

Valid Oracle8 ODBC driver logging values are shown in Table 26.1. To enable more than one type of logging, add the logging values and then add 1, as shown in the table. For example, to log ODBC entry points (a value of 2) and ODBC main functions (a value of 4), insert a value of 7 (2 + 4 + 1) after the equal sign.

The driver log, oraodbc.log, is written to the application program’s current working directory.

Start with a value of 17. It is usually the best Oracle8 ODBC driver logging value to start with for problem solving, because it logs only the SQL statements sent to the server and any errors that are returned from the server.