You can optimize application performance by adopting guidelines described in this section.
EncryptionMethod: Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) required to encrypt and decrypt data.
FetchSize/NativeFetchSize: The connection properties FetchSize and NativeFetchSize can be used to adjust the trade-off between throughput and response time. In general, setting larger values for FetchSize and NativeFetchSize will improve throughput, but can reduce response time.
For example, if an application attempts to fetch 100,000 rows from the native data source and NativeFetchSize is set to 500, the driver must make 200 round trips across the network to get the 100,000 rows. If, however, NativeFetchSize is set to 10000, the driver only needs to make 10 round trips to retrieve 100,000 rows. Network round trips are expensive, so generally, minimizing these round trips increases throughput.
For many applications, throughput is the primary performance measure, but for interactive applications, such as Web applications, response time (how fast the first set of data is returned) is more important than throughput. For example, suppose that you have a Web application that displays data 50 rows to a page and that, on average, you view three or four pages. Response time can be improved by setting FetchSize to 50 (the number of rows displayed on a page) and NativeFetchSize to 200. With these settings, the driver fetches all of the rows from the native data source that you would typically view in a single session and only processes the rows needed to display the first page.
Note: FetchSize provides a suggestion to the driver as to the number of rows it should internally process before returning control to the application. The driver may fetch fewer rows to conserve memory when processing exceptionally wide rows.
InsensitiveResultSetBufferSize: To improve performance, insensitive result set data can be cached instead of written to disk. If the size of the result set data is greater than the size allocated for the cache, the driver writes the result set to disk. The maximum cache size setting is 2 GB.
JVM Heap Size: JVM heap size can be used to address memory and performance concerns. By increasing the max Java heap size, you increase the amount of data the driver may accumulate 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 ResultMemorySize to -1, increasing the max Java heap size can improve performance by reducing the need to write to disk, or removing it altogether.
MaxPooledStatements: To improve performance, the driver's own internal prepared statement pooling should be enabled when the driver does not run from within an application server or from within another application that does not provide its own prepared statement pooling. When the driver's internal prepared statement pooling is enabled, the driver caches a certain number of prepared statements created by an application. For example, if the MaxPooledStatements property is set to 20, the driver caches the last 20 prepared statements created by the application. If the value set for this property is greater than the number of prepared statements used by the application, all prepared statements are cached. (See "Designing JDBC Applications for Performance Optimization" for more information about using prepared statement pooling to optimize performance.)
ResultMemorySize: ResultMemorySize can affect performance in two main ways. First, if the size of the result set is larger than the value specified for ResultMemorySize, the driver writes a portion of the result set to disk. Since writing to disk is an expensive operation, performance losses will be incurred. Second, when you remove any limit on the size of an intermediate result set by setting ResultMemorySize to 0, you can realize performance gains for result sets that easily fit within the JVM's free heap space. However, the same setting can diminish performance for result sets that barely fit within the JVM's free heap space.
