The connection options 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.
ConnectionReset
Purpose:
Determines whether the state of connections that are removed from the connection pool for reuse by the application is reset to the initial configuration of the connection.
Note: The value for this option is ignored when Connection Pooling is disabled (Pooling=0).
Performance Impact:
Resetting a re-used connection to the initial configuration settings impacts performance negatively because the connection must issue additional commands to the server.
Recommended Settings:
Set to 0 (Disabled) 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.
EnableServerSideCursors
Purpose:
Determines which server-side cursors are enabled for the data source. This option applies to Forward Only, Keyset, and Static cursors.
Performance Impact:
Using scrollable cursors is more expensive than forward-only cursors, and, therefore, can adversely impact performance. If your application does not always require the use of scrollable cursors, you can improve performance by restricting the use of server-side scrollable cursors using the this option.
Recommended Settings:
Configure this option to suit the needs of your application. If your application does not require scrollable cursors, set to 0 (Disabled) for the best 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 0 (No Encryption) for improved performance.
FailoverMode
Purpose:
Specifies the type of failover method the driver uses.
Performance Impact:
Although high availability that replays queries after a failure provides increased levels of protection, it can adversely affect performance because of increased overhead.
Recommended Settings:
If your application does not require the ability to recover or restart transactions, set this option to 0 (Connection) for improved performance.
LoadBalanceTimeout
Purpose:
Specifies the number of seconds to keep inactive connections open in a connection pool.
Note: The value for this option is ignored when Connection Pooling is disabled (Pooling=0).
Performance Impact:
By configuring LoadBalanceTimeout to keep inactive connections open, you improve the likelihood that the driver can reuse an existing connection instead of establishing new one when a connection is needed. This improves performance by avoiding the overhead required to establish a new connection.
The MinPoolSize option can cause some connections to ignore this value.
Recommended Settings:
Set to 0 for improved performance. By specifying a value of 0 (the default), inactive connections are kept open.
MaxPoolSize
Purpose:
The maximum number of connections allowed within a single connection pool. When the maximum number of connections is reached, no additional connections can be created in the connection pool.
Note: The value for this option is ignored when Connection Pooling is disabled (Pooling=0).
Performance Impact:
Setting the maximum number of connections that the pool can contain too low might cause delays while waiting for a connection to become available. Setting the number too high wastes memory resources.
Recommended Settings:
Tune this setting for your environment. Specify the maximum number of connections your environment requires to have open at a time.
MinPoolSize
Purpose:
Specifies the minimum number of connections that are opened and placed in a connection pool, in addition to the active connection, when the pool is created. The connection pool retains this number of connections, even when some connections exceed their LoadBalanceTimeout value.
Note: The value for this option is ignored when Connection Pooling is disabled (Pooling=0).
Performance Impact:
A connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size, if one has been specified. The connection pool retains this number of connections, even when some connections exceed their LoadBalanceTimeout value. By increasing this number, you improve the likelihood that the driver will be able to reuse a connection; thereby, improving performance.
Recommended Settings:
Tune this setting according to your environment. Specify the average number of connections your environment typically uses at a time.
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.
Pooling
Purpose:
Specifies whether to use the driver’s connection pooling.
Performance Impact:
Connection pooling allows you to reuse connections rather than creating a new one every time the driver needs to establish a connection to the underlying database. Using connection pooling can improve performance by avoiding the penalty incurred when establishing a new connection, especially for high-throughput applications.
You can further affect the performance by tuning the following connection options that are related to connection pooling: ConnectionReset, MaxPoolSize, MinPoolSize, and LoadBalanceTimeout.
Recommended Settings:
Enable this option to improve performance.
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 2005 and higher 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.
