SET CACHED_PROCEDURES

The SET CACHED_PROCEDURES statement specifies the number of stored procedures that the database engine caches in memory for a SQL session.

The value of number can be any whole number in the range zero through approximately two billion. The database engine automatically defaults to 50. Each session can change its number of cached procedures by issuing the SET statement.

•If you set both SET statements to zero, the database engine does not cache stored procedures. In addition, the engine removes any existing cache used for stored procedures. That is, the engine flushes from cache all stored procedures that were cached before you set both statements to zero.

•If you set only one of the statements to a value, either zero or a nonzero value, the other statement is implicitly set to zero. The statement implicitly set to zero is ignored. For example, if you are only interested in caching 70 procedures and are not concerned with the amount of memory, set CACHED_PROCEDURES to 70. The database engine implicitly sets PROCEDURES_CACHE to zero, which ignores the setting.

The following condition applies if you set CACHED_PROCEDURES to a nonzero value. The database engine removes the least-recently-used procedures from the cache if the execution of a procedure causes the number of cached procedures to exceed the CACHED_PROCEDURES value.

If a memory cache is used, it retains a compiled version of a stored procedure after the procedure executes. Typically, caching results in improved performance for each subsequent call to a cached procedure. Note that excessive memory swapping, or thrashing, could occur depending on the cache settings and the SQL being executed by your application. Thrashing can cause a decrease in performance.

In addition to the SET statement, the number of cached procedures can be specified with a registry setting. The registry settings apply to all sessions and provides a convenient way to set an initial value. Each session can override the registry setting for that particular session by using the SET statement.

The registry setting applies to all server platforms where Zen Enterprise Server or Cloud Server is supported. You must manually modify the registry setting. On Windows, use the registry editor provided with the operating system. On Linux and macOS, you can use the psregedit utility.

If the registry setting is not specified, the database engine automatically defaults to 50.

Note that in most Windows operating systems, the key is under HKEY_LOCAL_MACHINE\SOFTWARE\Actian\Zen. However, its location below HKEY_LOCAL_MACHINE\SOFTWARE can vary depending on the operating system.

3 Set CachedProcedures to the desired number of procedures that you want to cache.

►To set the cached procedures registry key in the Zen Registry on Linux and macOS

3 Set CachedProcedures to the desired number of procedures that you want to cache.

A stored procedure is not cached, regardless of the cache setting(s), for any of the following:

•The stored procedure references a local or a global temporary table. A local temporary table has a name that begins with the pound sign (#). A global temporary table has a name that begins with two pound signs (##). See CREATE (temporary) TABLE.

•The stored procedure contains any data definition language (DDL) statements. See Data Definition Statements.

•The stored procedure contains an EXEC[UTE] statement used to execute a character string, or an expression that returns a character string. For example: EXEC ('SELECT Student_ID FROM ' + :myinputvar).

The following example sets a cache memory of 2 MB that stores up to 20 stored procedures.

The following example sets a cache memory of 1,000 MB that stores up to 500 stored procedures.

The following example specifies that you do not want to cache stored procedures and that any existing procedures cache will be removed.

The following example specifies that you want to cache 120 stored procedures and ignore the amount of memory used for the cache.