The recommended setting for the client-side FetchCount parameter is the maximum number of rows that can fit in the Mule Fetch message, which is based on the MuleFetchMessageSize parameter and the row size.
FetchCount = floor (MuleFetchMessageSize / row size)
Setting the FetchCount parameter to a larger value causes the client to wait in a loop until the requested number of rows are fetched by using multiple round trips between the client and server.
Setting the FetchCount parameter to a smaller value does not make use of the full capacity of MuleFetchMessageSize.
This sample case provides an example of how to determine the best value for the FetchCount parameter:
Table Schema: "tab_1m_rows_fixed_column_small" Column | Type | Modifiers -----------+-------------------+----------- t_char | character(1024) |
- The size of each row: 1KB
- The number of rows to be fetched: 1,000,000
- The MuleFetchMessageSize parameter value: 1MB (1024KB)
Calculating the FetchCount:
FetchCount = floor (MuleFetchMessageSize / row size)
FetchCount = floor (1024 KB / 1KB)
FetchCount = 1024 (Therefore, the value of the FetchCount parameter should be set to a value of 1000 rows.)
This table shows that a value of 1000 for the FetchCount parameter provides the best total time result for this particular sample case:
FetchCount | 1M | ||
---|---|---|---|
ExecuteQuery Time (ms) | ResultFetch Time (ms) | Total Time (ms) | |
10 | 60 | 128048 | 128108 |
50 | 60 | 28524 | 28584 |
100 | 58 | 23362 | 23420 |
500 | 58 | 18424 | 18482 |
1000 | 60 | 14865 | 14925 |
5000 | 58 | 15645 | 15703 |
10000 | 56 | 15553 | 15609 |
50000 | 58 | 15986 | 16044 |
100000 | 57 | 16043 | 16100 |