Integer, 0 to 100

Can be set for an individual connection or for the PUBLIC group. Changing the value takes effect immediately.

10

Client statement caching reduces database requests and statement prepares when identical SQL statements are prepared multiple times. When the same SQL text is prepared and dropped repeatedly, the client caches the statement, leaving it prepared on the database server, even after it has been dropped by the application. Caching the statement saves the database server the extra work of dropping and re-preparing the statement. If a schema change occurs, a database option setting changes, or a DROP VARIABLE statement is executed, the prepared statement is dropped automatically and is prepared again the next time the SQL statement is executed, ensuring that a cached statement that could cause incorrect behavior is never reused.

This option specifies the maximum number of statements that can remain prepared (cached). Cached statements are not counted toward the max_statement_count resource governor.

The setting of this option applies to connections made using embedded SQL, ODBC, OLE DB, ADO.NET, and the SQL Anywhere JDBC driver. It does not apply to Sybase Open Client, jConnect, or HTTP connections.

Setting this option to 0 disables client statement caching. Increasing this value has the potential to improve performance if the application is repeatedly preparing and dropping more than ten of the same SQL statements. For example, if an application loops through twenty-five SQL statements, preparing and dropping them each iteration through the loop, and each iteration each of these SQL statements have the exact same text, setting this option to 25 may improve performance.

Increasing the value of this option increases memory use on the client and places more cache pressure on the database server. If a significant number of cached statements cannot be reused because of schema changes or option settings, statement caching is disabled automatically for the connection. If statement caching is automatically turned off, the client periodically turns statement caching on again to re-evaluate the decision and determine whether re-enabling statement caching would be beneficial.

When client statement caching is enabled, the max_client_statements_cached option should be set to 0 to disable client statement caching while the request log is captured if the log will be analyzed using the tracetime.pl Perl script.

• max_statement_count option
• ClientStmtCacheHits connection property
• ClientStmtCacheMisses connection property
• ClientStmtCacheHits server property
• ClientStmtCacheMisses server property
• max_client_statements_cached connection property