Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

How can I speed up data retrieval when using the ODBC-ODBC Bridge server?

If you wrote the application, using bound columns and large array sizes is a great deal more efficient over a network. The Easysoft ODBC-ODBC Bridge client pulls all the bound columns over in one go, which is a lot quicker than using repeated SQLFetchs and multiple SQLGetData calls, one per column.

If you don't want to change your application (or can't) and you're only reading data from the database (and not using positioned updates, deletes, and so on) then the ODBC-ODBC Bridge has a built in block-fetch mode, which may be enabled with the data source attribute, BlockFetchSize. Add BlockFetchSize = num to the data source entry you're using. num is the number of rows to retrieve in one go. This shows significant speed increases for the reasons in the paragraph above, but may not be used with certain types of cursors and when doing positioned updates or deletes.

Note that a value of 0 means block-fetch mode is turned off and the ODBC-ODBC Bridge works as a normal ODBC driver.

A value of 1 is safe to use even if you're doing positioned updates and deletes, and is often faster than with block-fetch mode turned off.

The ODBC-ODBC Bridge does the following tests to decide whether block-fetch mode is possible:

  1. Cursor type must be FORWARD_ONLY.
  2. RowArraySize must be 1.
  3. There mustn't be any bound columns.
  4. If the ODBC driver is Microsoft SQL Server, the application must support ODBC 3.0 or later.

    The Microsoft SQL Server ODBC driver does not return RowsProcessedPtr to an ODBC 2.0 application.

  5. The row bind offset must not have been set.
  6. You must not be using SQLExtendedFetch.

Note from the above checks, BlockFetchSize has no effect when the application or interface is using bound columns itself to retrieve data (for example Perl DBD::ODBC).