Adaptive Server performs the following steps to process ad hoc SQL statements using the statement cache:
Adaptive Server parses the statement.
If the statement should be cached (see “Caching conditions”), Adaptive Server computes a hash value from the statement. Adaptive Server uses this hash value to search for a matching statement in the statement cache (see “Statement matching criteria”).
If a match is found in the statement cache, Adaptive Server skips to step 4.
If a match is not found, Adaptive Server proceeds to step 2.
Adaptive Server caches the SQL statement text.
Adaptive Server wraps the SQL statement with a lightweight stored procedure and changes any local variables into procedure parameters. The internal representation of the lightweight procedure is not yet compiled into the plan.
Adaptive Server converts the SQL statement into an execute statement for the corresponding lightweight procedure.
If there is no plan in the cache, Adaptive Server compiles the procedure and caches the plan. Adaptive Server compiles the plan using the assigned runtime values for the local variables.
If the plan exists but is invalid, Adaptive Server returns to step 3 using the text of the cached SQL statement.
Adaptive Server then executes the procedure. Substituting the lightweight procedure increments the @@nestlevel global variable.
Adaptive Server matches an ad hoc SQL statement to a cached statement by the SQL text and by login (particularly if both users have sa_role), user ID, database ID, and session state settings. The relevant session state consists of settings for the following set command parameters:
forceplan
jtc
parallel_degree
prefetch
quoted_identifier
sort_merge
table count
transaction isolation level
chained (transaction mode)
Settings for these parameters determine the behavior of the plan Adaptive Server produces for a cached statement. For information on the set command and its parameters, see the Adaptive Server Reference Manual.
You must configure set chained on/off in
its own batch if you enable the statement cache.
Adaptive Server currently caches select, update, delete, and insert select statements with at least one table reference.
Statements are not cached if the abstract plan dump or abstract plan load parameters are enabled. That is, you cannot enable the statement cache and enable the abstract plan load and abstract plan dump at configuration parameters the same time
Adaptive Server does not cache select into statements, cursor statements, dynamic statements, plain insert (not insert select) statements, and statements within stored procedures, views, and triggers. Statements that refer to temporary tables are not cached, nor are statements with language parameters transmitted as BLOB datatypes. Statements that are prohibitively large are not cached. Also, select statements that are part of a conditional if exists or if not exists clause are not cached.
