Specifies the maximum number of rows that the driver processes before returning data to the application for a single fetch request when executing a Select. This value provides a suggestion to the driver as to the number of rows that should be returned to the application. The driver may fetch fewer rows to conserve memory when processing exceptionally wide rows.
Valid Values
0 | x
where:
x
is a positive integer.
Behavior
If set to 0, the driver fetches and processes all of the rows of the result before returning control to the application. Setting Fetch Size to 0 may diminish performance and increase the likelihood of out-of-memory errors.
If set to x, the driver limits the number of rows that may be processed and returned to the application for a single fetch request. To optimize throughput, the driver uses an internal algorithm to determine how many rows should be returned based on the width of rows in the result set. Therefore, the driver may return fewer rows when the result set contains wide rows. Alternatively, the driver may return the maximum number of rows as specified by Fetch Size when the result set contains narrow rows.
Notes
The Fetch Size connection option provides a suggestion to the driver as to the number of rows that should be returned to the application, but it is not a determining factor when exceptionally wide rows are being processed. When exceptionally wide rows are being processed, the driver reduces the number of rows returned but uses an algorithm to maximize throughput. When rows are not exceptionally wide, Fetch Size can be used to adjust the trade-off between throughput and response time. Smaller fetch sizes can improve the initial response time of the query. Larger fetch sizes can improve overall response times at the cost of additional memory.
By lowering the maximum number of rows as specified by Fetch Size, you lower the number of rows the driver is required to process before returning data to the application. Thus, you reduce demands on the driver's internal memory, and, in turn, decrease the likelihood of out-of-memory errors.
The Result Memory Size connection option can also be used to reduce demands on the driver's internal memory and decrease the likelihood of out-of-memory errors.