Set 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.
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 can fit in the current fetch buffer, setting it has 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.
- 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, before sending the fetch request to the database server. When both of these are enabled, this information is unavailable 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 protocols used for fetching arrays. The database server does not support simple large object columns in a scroll cursor. An error is returned.
- When using SQLGetData
In order for the driver to use 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 before calling SQLGetData. SQLSetPos is only usable with scroll cursors and simple-large-object columns are not allowed in scroll cursors. Also according to the standard, SQLGetData must not be used 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.