Specifies the maximum size, in megabytes, of an intermediate result set that the driver holds in memory. When this threshold is reached, the driver writes a portion of the result set to disk in temporary files.
Valid Values
-1 | 0 | x
where:
x
is a positive integer.
Behavior
If set to -1, the maximum size of an intermediate result set that the driver holds in memory is determined by a percentage of the max Java heap size. When this threshold is reached, the driver writes a portion of the result set to disk.
If set to 0, the SQL Engine holds intermediate results in memory regardless of size. Setting Result Memory Size to 0 can increase performance for any result set that can easily fit within the JVM's free heap space, but can decrease performance for any result set that can barely fit within the JVM's free heap space.
If set to x, the SQL Engine holds intermediate results in memory that are no larger than the size specified. When this threshold is reached, the driver writes a portion of the result set to disk.
Notes
By default, Result Memory Size is set to -1. When set to -1, the maximum size of an intermediate result that the driver holds in memory is a percentage of the max Java heap size. When processing large sets of data, out-of-memory errors can occur when the size of the result set exceeds the available memory allocated to the JVM. In this scenario, you can tune Result Memory Size to suit your environment. To begin, set Result Memory Size equal to the max Java heap size divided by 4. Proceed by decreasing this value until out-of-memory errors are eliminated. As a result, the maximum size of an intermediate result set the driver holds in memory will be reduced, and some portion of the result set will be written to disk. Be aware that while writing to disk reduces the risk of out-of-memory errors, it also negatively impacts performance. For optimal performance, decrease this value only to a size necessary to avoid errors.
You can also address memory and performance concerns by adjusting the max Java heap size using the JVM Arguments connection option. By increasing the max Java heap size, you increase the amount of data the driver accumulates in memory. This can reduce the likelihood of out-of-memory errors and improve performance by ensuring that result sets fit easily within the JVM's free heap space. In addition, when a limit is imposed by setting Result Memory Size to -1, increasing the max Java heap size can improve performance by reducing the need to write to disk, or removing it altogether.
The Fetch 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.
By default, Result Memory Size is set to -1. When set to -1, the maximum size of an intermediate result that the driver holds in memory is a percentage of the max Java heap size. When processing large sets of data, out-of-memory errors can occur when the size of the result set exceeds the available memory allocated to the JVM. In this scenario, you can tune Result Memory Size to suit your environment. To begin, set Result Memory Size equal to the max Java heap size divided by 4. Proceed by decreasing this value until out-of-memory errors are eliminated. As a result, the maximum size of an intermediate result set the driver holds in memory will be reduced, and some portion of the result set will be written to disk. Be aware that while writing to disk reduces the risk of out-of-memory errors, it also negatively impacts performance. For optimal performance, decrease this value only to a size necessary to avoid errors.