Performance Considerations

Encrypt: Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) required to encrypt and decrypt data.

Max Statement Cache Size: Caching all of the prepared statements that an application uses might appear to offer the best performance. However, this approach may come at a cost of database server memory if you implement statement caching with connection pooling. In this case, each pooled connection has its own statement cache, which may contain all of the prepared statements used by the application. All of the cached prepared statements are also maintained in the database server's memory.

Pooling: If you enable the data provider to use connection pooling, you can define additional options that affect performance:

•

Load Balance Timeout: You can define how long to keep connections in the pool. The pool manager checks a connection's creation time when it is returned to the pool. The creation time is compared to the current time, and if the timespan exceeds the value of the Load Balance Timeout option, the connection is destroyed. The Min Pool Size option can cause some connections to ignore this value.

•

Connection Reset: Resetting a re-used connection to the initial configuration settings impacts performance negatively because the connection must issue additional commands to the server.

•

Max Pool Size: 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 resources.

•

Min Pool Size: 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 Load Balance Timeout value.

Schema Options: Returning some types of database metadata can affect performance. To optimize application performance, the data provider prevents the return of performance-expensive database metadata such as procedure definitions or view definitions. If your application needs this database metadata, you can specifically request its return.

To return more than one type of the omitted metadata, specify either a comma-separated list of the names, or the sum of the hexadecimal values of the column collections that you want to return. For example, to return procedure definitions and view definitions, specify one of the following:

Statement Cache Mode: In most cases, enabling statement caching results in improved performance. To enable the caching of prepared statements (Command instances), set this option to Auto. Use this setting if your application has marked prepared statements for implicit inclusion in the statement cache, or has marked some statements for implicit inclusion and others for explicit inclusion. If you want the statement cache to include only prepared statements that are marked Cache, 1) set the StatementCacheBehavior property of the Command object to Cache and 2) set this option to ExplicitOnly.

StatementCacheBehavior: If your application reuses prepared statements multiple times over an application’s lifetime, you can influence performance by using a statement cache. This property identifies how a prepared statement (a Command object instance) is handled during statement caching.