Easysoft ODBC-ODBC Bridge

I only want to retrieve data from the server, are there any tricks to speed it up in read-only mode?

Article:
00965
Last Reviewed:
10th January 2007
Revision:
1

If you have written the application yourselves then using bound columns and large Array sizes is a great deal more efficient over a network. The OOB 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 do not want to change your application (or cannot) and you are only reading data from the database (and not using positioned updates, deletes etc) then the OOB has a built in block-fetch-mode which may be enabled with the DSN attribute, BlockFetchSize. Add "BlockFetchSize = n" to the DSN entry you are using where (0 <= n < 100) n 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/deletes.

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

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

The OOB performs the following tests to decide whether BlockFetchMode 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 driver is MS SQL Server then the application must be ODBC 3.

    The MS SQL Server driver will not return RowsProcessedPtr to an ODBC 2.0 app.

  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 (e.g. Perl's DBD::ODBC).

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.