SET PROCEDURES

SET PROCEDURES_CACHE

The SET PROCEDURES_CACHE statement specifies the amount of memory for an SQL session that the database engine reserves as a cache for stored procedures.

Syntax

SET PROCEDURES_CACHE = megabytes

Remarks

The value of megabytes can be any whole number in the range zero to approximately two billion. The database engine automatically defaults to 5 MB. Each session can change its amount of cache memory by issuing the SET statement.

The companion statement to SET PROCEDURES_CACHE is SET CACHED_PROCEDURES.

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 30 MB as the amount of memory cached and are not concerned with the number of procedures cached, set PROCEDURES_CACHE to 30. The database engine implicitly sets CACHED_PROCEDURES to zero, which ignores the setting.

The following condition applies if you set PROCEDURES_CACHE to a nonzero value. The database engine removes the least-recently-used procedures from the cache if the execution of a procedure allocates memory that exceeds the PROCEDURES_CACHE 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.

Registry Setting

In addition to the SET statement, the amount of memory reserved for the cache 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 on which Pervasive PSQL Server is supported. You must manually modify the registry setting. On Windows, use the registry editor provided with the operating system. On Linux, you can use the psregedit utility. On NetWare, use a text editor to modify SYS:ETC\PSRGSTRY.INI.

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

To specify the amount of cache memory in a registry setting on Windows:

Locate the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Pervasive Software\SQL 
Relational Engine 
 


 Note


In most Windows operating systems, the `Pervasive Software' key is HKEY_LOCAL_MACHINE\SOFTWARE\PERVASIVE SOFTWARE. However, its location under HKEY_LOCAL_MACHINE\SOFTWARE can vary depending on the operating system.

For this key, create a new string valued named ProceduresCache.

Set ProceduresCache to the desired amount of memory that you want to cache.

To specify the amount of cache memory in the Pervasive Registry (Linux or NetWare):

Locate the following key:

PS_HKEY_CONFIG\SOFTWARE\Pervasive Software\SQL 
Relational Engine

For this key, create a new string valued named ProceduresCache.

Set ProceduresCache to the desired amount of memory that you want to cache.

Caching Exclusions

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

Examples

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

SET CACHED_PROCEDURES = 20 
SET PROCEDURES_CACHE = 2

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

SET CACHED_PROCEDURES = 500 
SET PROCEDURES_CACHE = 1000

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

SET CACHED_PROCEDURES = 0 
SET PROCEDURES_CACHE = 0

The following example specifies that you want to set the amount of cache memory to 80 MB and ignore the number of procedures that may be cached.

SET PROCEDURES_CACHE = 80

(The database engine implicitly sets CACHED_PROCEDURES to zero.)