Kbase P116579: How to determine the optimal setting for the "Fetch Array Size" feature in the SQL-92 ODBC Driver
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/10/2010 |
|
Status: Verified
GOAL:
How to determine the optimal setting for the "Fetch Array Size" feature in the SQL-92 ODBC Driver ?
GOAL:
What has to be checked when determining the Fetch Array size?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
FIX:
In order to optimize the Fetch Array Size parameter an adjust and monitor method must be used.
The Fetch Array Size parameter sets the maximum number of rows that are permitted in a FETCH procedure and is set at the ODBC DataSource level. The Fetch Array Size tells the server how many result set rows to pack into a logical network message.
An optimal value can vary from query to query since it is affected by how many columns are in the result set, and the size of the columns in the result set. The size is limited only by the available memory of client and server. Since Array Fetch Size defines the number of rows returned to the client in a single fetch request to the server, the following determines the resources used based on Array Fetch Size:
a) the number of columns in a result set
b) the aggregate size (sql-width) of the columns in the result set.
Note this is not the aggregate size of the data. As with any SQL implementation, the client and the server must size for the maximum data size per column
c) the array fetch size
Using the following query as an example:
SELECT custnum, name, address, city FROM pub.customer;
With the following sizing information:
Column Type Size
custnum integer 4
name varchar 20
address varchar 30
city varchar 20
The array size for the data a row of the result set is 74 bytes. Using a default array fetch size of 50 will retrieve 50 rows at a time from the server. The client will use around 4k (3700 bytes for the data, plus some overhead for column labels, data type identifiers, null indicators, etc).
Setting the Fetch Array Size to a very large value can result in a very large amount of memory being used, depending on machine capacity, that can lead to paging and performance problems if pushed too far. To look at memory usage, connect a DBVisualizer client to see the column definitions (the desired size information). Then use WinSQL to experiment with queries (result set columns) and look at memory usage. A new connection should be made in between varying queries to get a good picture.