Using Statement Caching

A physical connection may own a statement cache, also called a statement pool. A statement cache is a group of prepared statements or instances of Command objects that can be reused by an application. Prepared statements are placed in the statement cache after their initial execution. Statements remain in the cache until the physical connection is closed or the maximum size is reached.

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).

Statement caching typically does not affect application code. If you use prepared statements and turn on statement caching, when the prepared statement is closed, it is placed in the statement cache for reuse instead of actually being 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 that use the Progress DataDirect Connect for ADO.NET data providers without changing any application code, 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.

The following table summarizes the effects of the statement caching settings.

Summary of Statement Cache Behavior

See the individual data provider sections for information on the options that are supported for each data provider.

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.