The connection properties described in this section directly affect the performance of your driver. To tune for performance, configure your driver according to the recommended settings and your environment.
ApplicationIntent
Purpose:
Specifies whether the driver connects to read-write databases or requests read-only routing to connect to read-only database replicas.
Performance Impact:
Querying read-only database replicas when possible, you can improve efficiency of your environment by reducing the work load on read-write nodes.
Recommended Settings:
If your application does not require write access, set to ReadOnly for improved performance.
EnableBulkLoad
Purpose:
Specifies whether the driver is permitted to use the bulk load protocol when executing a parameter batch for an INSERT statement.
Performance Impact:
For batch inserts, the driver might be able to use the native bulk load functionality instead of the database’s parameter batch mechanism. Using the bulk load functionality typically improves performance, but at the expense of potentially bypassing data integrity constraints.
Recommended Settings:
If your application does not require a high level of data integrity, enable this option for improved performance.
EncryptionMethod
Purpose:
Determines the method the driver uses to encrypt data sent between the driver and the database server.
Performance Impact:
Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) that is required to encrypt and decrypt data.
Recommended Settings:
If data encryption is not required, set to noEncryption for improved performance.
InsensitiveResultSetBufferSize
Purpose:
Determines the amount of memory that is used by the driver to cache insensitive result set data.
Performance Impact:
To improve performance when using scroll-insensitive result sets, the driver can cache the result set data in memory instead of writing it to disk. By default, the driver caches 2 MB of insensitive result set data in memory and writes any remaining result set data to disk. Performance can be improved by increasing the amount of memory used by the driver before writing data to disk or by forcing the driver to never write insensitive result set data to disk. The maximum cache size setting is 2 GB.
Recommended Settings:
Specify a value in KB that is a power of 2 for improved performance. This value should not exceed the amount available. The maximum value for this property is 2 GB.
LongDataCacheSize
Purpose:
Determines whether the driver caches long data (images, pictures, long text, binary data, or XML data) in result sets.
Performance Impact:
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.
Recommended Settings:
If your application does not retrieve long data column values out of order, disable caching long data in result sets (LongDataCacheSize=-1) for improved performance.
MaxPooledStatements
Purpose:
Specifies the maximum number of prepared statements to be pooled for each connection and enables the driver’s internal prepared statement pooling when set to an integer greater than zero (0).
Performance Impact:
The driver’s internal prepared statement pooling provides performance benefits when the driver is not running from within an application server or another application that provides its own statement pooling.
Recommended Settings:
For better performance, specify a value that is greater than the number of prepared statements used by the application. Note that this performance benefit comes at the expense of greater memory consumption.
PacketSize
Purpose:
Determines the number of bytes for each database protocol packet that is transferred from the database server to the client machine.
Performance Impact:
Typically, it is optimal for the client to use the maximum packet size that the database 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 the PacketSize attribute is set to the maximum packet size of the server.
Recommended Settings:
The optimal value depends on the typical size of data that is inserted, updated, or returned by the application and the environment in which it is running. Typically, larger packet sizes work better for large amounts of data. For example, if an application regularly returns character values that are 10,000 characters in length, using a value of 32 (16 KB) typically results in improved performance.
ResultSetMetaDataOptions
Purpose:
Determines whether the driver returns table name information in the ResultSet metadata for Select statements.
Performance Impact:
Returning table name information using the ResultSetMetaData.getTableName() method requires the driver to perform emulations to determine the correct table name for each column in the result set. This additional processing can adversely affect performance.
If set to 1 and the ResultSetMetaData.getTableName() method is called, the driver performs additional processing to determine the correct table name for each column in the result set. The driver returns schema name and catalog name information when the ResultSetMetaData.getSchemaName() and ResultSetMetaData.getCatalogName() methods are called if the driver can determine that information.
Recommended Settings:
If your application does not require returning table name information in the ResultSet metadata , you can improve performance by setting this option to 0.
SelectMethod
Purpose:
A hint to the driver that determines whether the driver requests a database cursor for Select statements.
Performance Impact:
In most cases, using server-side database cursors negatively impacts performance by requiring additional network trips when scrolling through results and increased demands on server resources. However, in some scenarios, such as retrieving large amounts of data, server-side cursors can offer improved performance and behavior.
Recommended Settings:
Set to cursor if following variables apply to your application; otherwise, set to direct (default):
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.
Large result sets use forward-only cursors.
SnapshotSerializable
Purpose:
Allows your application to use the snapshot isolation level if your Microsoft SQL Server database is configured for Snapshot isolation.
Note: You must have your Microsoft SQL Server database configured for snapshot isolation for this connection option to work.
Performance Impact:
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 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 option is enabled, performance is improved due to increased concurrency.
Recommended Settings:
Set this option to true for improved performance.
UseServerSideUpdatableCursors
Purpose:
Determines whether the driver uses server-side cursors when an updatable result set is requested.
Performance Impact:
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.
Recommended Settings:
If supported by your environment, set to true for improved performance.
Your application contains queries that retrieve large amounts of data.