Performing Ad Hoc Query Processing

When SAP ASE caches a statement, it changes the statement from an ad hoc query to a lightweight stored procedure.

For example, if you issue sp_bindefault or sp_bindrule in the same batch as the statement that invokes the default or rule without caching the statement, SAP ASE issues error message 540. However, if you cache the statement, SAP ASE binds the default or rule to the column.

SAP ASE may issue runtime errors instead of normalization errors when statements are cached and executed as stored procedures. For example, this query raises error number 241 if the statement is not cached, but raises a Truncation error and aborts the command if the statement is cached.
create table t1(c1 numeric(5,2)
go
insert t1 values(3.123)
go

To process ad hoc SQL statements using the statement cache:

  1. SAP ASE parses the statement.

    If the statement should be cached, SAP ASE computes a hash value from the statement. SAP ASE uses this hash value to search for a matching statement in the statement cache.

    • If a match is found in the statement cache, SAP ASE skips to step 4.

    • If a match is not found, SAP ASE proceeds to step 2.

  2. SAP ASE caches the SQL statement text.
  3. SAP ASE 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.
  4. SAP ASE converts the SQL statement into an execute statement for the corresponding lightweight procedure.
  5. If there is no plan in the cache, SAP ASE compiles the procedure and caches the plan. SAP ASE compiles the plan using the assigned runtime values for the local variables. If the plan exists but is invalid, SAP ASE returns to step 3 using the text of the cached SQL statement
  6. SAP ASE then executes the procedure. Substituting the lightweight procedure increments the @@nestlevel global variable.
Parent topic: Setting the Statement Cache