Application Using Threads (ApplicationUsingThreads): The driver coordinates concurrent database operations (operations from different threads) by acquiring locks. Although locking prevents errors in the driver, it also decreases performance. If your application does not make ODBC calls from different threads, the driver has no reason to coordinate operations. In this case, the ApplicationUsingThreads attribute should be disabled (set to 0).
Note: If you are using a multi-threaded application, you must enable the Application Using Threads option.
Fetch Size (FetchSize) and Native Fetch Size (NativeFetchSize): The connection options Fetch Size and Native Fetch Size can be used to adjust the trade-off between throughput and response time. In general, setting larger values for Fetch Size and Native Fetch Size 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 Native Fetch Size is set to 500, the driver must make 200 round trips across the network to get the 100,000 rows. If, however, Native Fetch Size 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 Fetch Size to 50 (the number of rows displayed on a page) and Native Fetch Size 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: Fetch Size 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.
JVM Arguments (JVMArgs): Used in conjunction with the Result Memory Size connection option, you can 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.
Read Consistency (ReadConsistency): The Read Consistency connection option manages the trade-off between the consistency and availability of your data. By setting Read Consistency to all, data is returned to the application only after all replicas have responded. With this setting, the data returned is highly consistent. However, it may take longer for data to be returned to the application, and, in some scenarios, operation timeouts can occur. In contrast, setting Read Consistency to quorum (default) or one reduces the number of replicas required to respond to a read request. While the data may not be as consistent, results are returned more quickly to the application.
Result Memory Size (ResultMemorySize): Result Memory Size can affect performance in two main ways. First, if the size of the result set is larger than the value specified for Result Memory Size, 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 Result Memory Size 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.
Write Consistency (WriteConsistency): The Write Consistency connection option determines the number of replicas on which the write must succeed before returning an acknowledgment to the client application. By setting this option to all, a write must succeed on all replica nodes in the cluster for that partition key. This setting provides high consistency. However, it may take longer for the successful execution of a write operation. In contrast, setting Write Consistency to quorum (default) or one reduces the number of replicas that must acknowledge the completion of a write command. While the data may not be as consistent across clusters, the write operation is completed more quickly.
