Look at the online help for the driver: "Improving Concurrency and Performance"
A quick summary (the full text):
To improve concurrency and performance of your application, perform the following:
· 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.
· If you are performing transactions that do not update the data source, set the SQL_ATTR_ACCESS_MODE attribute of the ODBC SQLSetConnectAttr function to SQL_MODE_READ_ONLY.
· If you are not using ODBC escape clauses, set the SQL_ATTR_NOSCAN attribute of the ODBC SQLSetConnectAttr function or the ODBC SQLSetStmtAttr function to true.
· Use the ODBC SQLFetchScroll function instead of the ODBC SQLFetch function for retrieving data from tables that have a large number of rows.
· Set the Prefetch Count in the Oracle Options tab of the Oracle ODBC Driver Configuration dialog box to a value greater than 1. 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.
· Set the Enable Thread Safety in the Applications Options tab of the Oracle ODBC Driver Configuration dialog box to disable if your application does not use threads. In this case, there is no need to incur the overhead associated with making the Oracle8 ODBC Driver thread safe.
· Set the Enable LOBs in the Oracle Options tab of the Oracle ODBC Driver Configuration dialog box to disable if your application does not update Oracle LOBs. There is a small performance penalty for INSERT and UPDATE statements when LOBs are enabled.
· Set the Enable Result Sets in the Application Options tab of the Oracle ODBC Driver Configuration dialog box to disable if your application does not use Result Sets. There is a small performance penalty for procedures called from packages not containing Result Sets.
· Set the Force Retrieval of Long Columns in the Workarounds Options tab of the Oracle ODBC Driver Configuration dialog box to disable if your application does not need to perform another operation before it has completed reading the entire long column. This will save on client memory consumption.
· Set the Enable Closing Cursors in the Application Options tab of the Oracle ODBC Driver Configuration dialog box to disable if it is not necessary for the database server to immediately close the cursor. There is a small performance penalty to force the server to close the cursor.
· Leave SQLGetData extensions disabled unless required. Supporting SQLGetData extensions implicitly requires Force Retrieval of Long Columns to be enabled.
==========================
Okay you have other options, for instance Oracle Objects for Ole sits right above the OCI layer and it is very fast. The Oracle Provider for OLE DB is also very fast (available in 8.1.6), much faster than ODBC.
I hope these tips solve your problems,
dBalaski
Main Topics
Browse All Topics
by: payperpagePosted on 2002-02-01 at 04:48:37ID: 6771093
Merant.com do the best odbc drivers. There is a reveiew in a recent Oracle magazine about how Merant are certified to run the ODBC at a lower level than is generally possible, so bypass some of the crap that slows it down. I would recommend giving them a try. I think they call their Oracle drivers DataDirect.