Setting the Fetch Array Size for Simple-Large-Object Data

To reduce the network overhead for fetches involving multiple rows of simple-large-object data, you can set the array size so when the driver receives a multiple-row fetch request, it optimizes the fetch buffer size and the internal fetch array size, and eliminates a round trip to the database server for every simple large object. Setting the array size greater than 1 can result in a performance improvement even for other types of data because it has the side effect of automatically increasing the fetch buffer size if necessary. (If the number of rows specified will fit in the current fetch buffer, setting it will have little effect.)

An application can request that multiple rows be returned to it by setting the statement attribute SQL_ATTR_ROW_ARRAY_SIZE or setting the ARD header field SQL_DESC_ARRAY_SIZE to a value greater than one, and then calling either SQLFetch or SQLFetchScroll. (The default value of SQL_ATTR_ROW_ARRAY_SIZE is one.) The driver then recognizes when it receives a multiple-row fetch request and optimizes the settings for the fetch buffer size and the internal fetch array size. Settings for these are based on the internal tuple size, the user setting of row array size, and the current setting of fetch array size.

You cannot use the internal fetch array feature under the following conditions:

• When OPTOFC and deferred-PREPARE are both enabled

  To use the fetch array feature, the driver is dependent upon knowing how large a row is going to be, as received from the database server, prior to sending the fetch request to the database server. When both of these are enabled, this information is not available until after a fetch is performed.

• When using scroll cursors

  There are separate internal client-to-server protocols used for scroll cursors that are distinct from those used for fetching arrays. The database server does not support simple large object columns in a scroll cursor. An error will be returned.

• When using SQLGetData

  In order for the driver to utilize the fetch array feature, it has to be able to tell the database server how much data it is prepared to receive at the time of the fetch request. Calls to SQLGetData take place after SQLFetch.

  According to the ODBC standard, when using block cursors, the application must call SQLSetPos to position the cursor on a particular row prior to calling SQLGetData. SQLSetPos is only usable with scroll cursors and, as above, simple-large-object columns are not allowed in scroll cursors. Also according to the standard, SQLGetData must not be used in conjunction with a forward-only cursor with a rowset size greater than 1.

  The alternative to using SQLGetData is to use SQLBindCol, which would come before the call to SQLFetch.

You might want to optimize use of SQL_ATTR_ROW_ARRAY_SIZE so the application sets the value of it according to the maximum number of rows that can be transported in a single buffer. After a statement is prepared, the application might call SQLGetStmtAttr to get the value of SQL_INFX_ATTR_FET_ARR_SIZE. If the data fits in one fetch buffer, the internal setting of SQL_INFX_ATTR_FET_ARR_SIZE equals the application setting of SQL_ATTR_ROW_ARRAY_SIZE. In practice, this is only useful on large result sets.