You can optimize your application’s performance if you set the SQL Server driver connection properties as described in this section:
Always Encrypted: The following options related to the Always Encrypted feature affect performance:
ColumnEncryption: Due to the overhead associated with encrypting and decrypting data, Always Encrypted functionality can adversely affect performance when enabled. If your application does not require access to encrypted columns, you can disable this property (ColumnEncryption=Disabled) for improved performance. Alternatively, if your application only needs to retrieve and decrypt columns, not update them, you can improve performance over the behavior of the Enabled setting by specifying a value of ResultsetOnly for this property. Note that when using this setting, queries containing parameters that affect encrypted columns will return an error.
AEKeyCacheTTL: When Always Encrypted functionality is enabled (ColumnEncryption=Enabled | ResultsetOnly), you can determine how long, in seconds, column encryption keys are cached using the AEKeyCacheTTL property. Caching column encryption keys can provide performance gains by reducing the overhead associated with fetching and decrypting keys for the same data multiple times during a connection. Specifying larger values for this option increases the length of time that a column encryption key persists in the cache; therefore, improving performance in some scenarios. Alternatively, by specifying a value of -1, you can configure the driver to persist keys for the life of the connection. Note that column encryption keys are designed to be deleted from the cache as a security measure and should not be stored for long periods of time.
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.
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.
Large result sets use forward-only cursors.
SnapshotSerializable: 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 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.
ColumnEncryption: Due to the overhead associated with encrypting and decrypting data, Always Encrypted functionality can adversely affect performance when enabled. If your application does not require access to encrypted columns, you can disable this property (ColumnEncryption=Disabled) for improved performance. Alternatively, if your application only needs to retrieve and decrypt columns, not update them, you can improve performance over the behavior of the Enabled setting by specifying a value of ResultsetOnly for this property. Note that when using this setting, queries containing parameters that affect encrypted columns will return an error.