Using Statement Caching

Using Statement Caching

A statement cache is a group of prepared statements or instances of Command objects that can be reused by an application. Using statement caching can improve application performance because the actions on the prepared statement are performed once even though the statement is reused multiple times over an application’s lifetime. You can analyze the effectiveness of the statements in the cache (see Analyzing Performance With Connection Statistics).

A statement cache is owned by a physical connection. After being executed, a prepared statement is placed in the statement cache and remains there until the connection is closed.

Statement caching can be used across multiple data sources and can be used beneath abstraction technologies such as the Microsoft Enterprise Libraries with the Data Access Application Blocks.

Enabling Statement Caching

By default, statement caching is not enabled. To enable statement caching for existing applications, set the Statement Cache Mode connection string option to Auto. In this case, all statements are eligible to be placed in the statement cache.

You can also configure statement caching so that only statements that you explicitly mark to be cached are placed in the statement cache. To do this, set the StatementCacheBehavior property of the statement’s Command object to Cache and set the Statement Cache Mode connection string option to ExplicitOnly.

Table 2 summarizes the statement caching settings and their effects.

Table 2

Summary of Statement Cache Behavior

StatementCacheBehavior

Statement Cache Mode

Explicitly add the statement to the statement cache.

ExplicitOnly (the default)

Add the statement to the statement cache. If necessary, the statement is removed to make room for a statement marked Cache.

Implicit (the default)

Specifically exclude the statement from the statement cache.

Auto or ExplicitOnly

Choosing a Statement Caching Strategy

Statement caching provides performance gains for applications that reuse prepared statements multiple times over the lifetime of an application. You set the size of the statement cache with the Max Statement Cache Size connection string option. If space in the statement cache is limited, do not cache prepared statements that are used only once.

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 memory if you implement statement caching with connection pooling. In this case, each pooled connection has its own statement cache that may contain all of the prepared statements used by the application. All of these pooled prepared statements are also maintained in the database’s memory.