You can optimize your application’s performance if you set the SQL Server driver connection properties as described in this section:
ApplicationIntent: You can shift load away from the read-write nodes of your database cluster to read-only nodes by setting this connection property to readOnly and querying read-only database replicas when possible.
EnableBulkLoad: For batch inserts, the driver can use native bulk load protocols instead of the batch mechanism. Bulk load bypasses the data parsing usually done by the database, providing an additional performance gain over batch operations. Set this property to true to allow existing applications with batch inserts to take advantage of bulk load without requiring changes to the code.
EncryptionMethod: Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) required to encrypt and decrypt data.
InsensitiveResultSetBufferSize: To improve performance, 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.
LongDataCacheSize: To improve performance when your application retrieves images, pictures, long text, binary data, or XML data, you can disable caching for long data on the client if your application retrieves long data column values in the order they are defined in the result set. If your application retrieves long data column values out or order, long data values must be cached.
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.
Refer to "Designing JDBC Applications for Performance Optimization" in the DataDirect Connect Series for JDBC Reference for more information about using prepared statement pooling to optimize performance.
PacketSize: Typically, it is optimal for the client to use the maximum packet size that the server allows. This reduces the total number of round trips required to return data to the client, thus improving performance. Therefore, performance can be improved if this property is set to the maximum packet size of the database server.
ResultSetMetaDataOptions: The driver’s performance may be adversely affected if you set this option to 1. If set to 1 and the ResultSetMetaData.getTableName method is called, the driver performs emulations which take additional processing.
SelectMethod: In most cases, using server-side database cursors impacts performance negatively. However, if the following four variables are true in your application, the best setting for this property is cursor, which means use server-side database cursors:
Your application contains queries that retrieve large amounts of data.
Your application executes a SQL statement before processing or closing a previous large result set and does this multiple times.
Snapshot Isolation provides transaction-level read consistency and an optimistic approach to data modifications by not acquiring locks on data until data is to be modified. This Microsoft SQL Server 2005 and higher feature can be useful if you want to consistently return the same result set even if another transaction has changed the data and 1) your application executes many read operations or 2) your application has long running transactions that could potentially block users from reading data. This feature has the potential to eliminate data contention between read operations and update operations. When this connection property is set to true (thereby, you are using Snapshot Isolation), performance is improved due to increased concurrency.
UseServerSideUpdatableCursors: In most cases, using server-side updatable cursors improves performance. However, this type of cursor cannot be used with insensitive result sets or with sensitive results sets that are not generated from a database table that contains a primary key.