http://goo.gl/EmKxy0

Home > ASE > Query Processing and Abstract plan of stored procedure

Query Processing and Abstract plan of stored procedure

Query — When does the optimization of statements in a stored procedure transpired, at compile time or at run time?

Answer — ASE 15.0.2 and later defers the query optimization of stored procedures until it execute the statement.

 

Query — Why does stored procedure runs slower at first execute?

Answer — Stored procedure runs slower at first execute because it performs the optimization of query and creates query plan and stores the query plan in cache. Hence, In subsequent  run it picks up the existing query plan from the cache and runs faster.

 

Query — What is the difference between stored procedure’s query plans of ASE 12.5.0 and ASE 15.0.2/later?

Answer — ASE versions before 15.0.2 used to create query plan of stored procedure at compile time wherein values of variables were not available, thereby sometimes stored procedure would not run as expected.

However, in versions 15.0.2 and later,execution engine step has been segregated into 2 steps 1. Procedural execution engine and 2. Query execution engine

The procedural engine executes command statements such as create table, execute procedure, and declare cursor directly. For data manipulation language (DML) statements, such as select, insert, delete, and update, the engine sets up the execution environment for all query plans and calls the query execution engine.

The query execution engine executes the ordered steps specified in the query plan provided by the code generator, which have the values of variables used in stored procedure. Hence, now query plans of stored procedure are more accurate than query plans of stored procedure in previous versions.

Courtesy: Sybooks

Check the Sybase Wiki @ sybasewiki.com
  1. No comments yet.
  1. No trackbacks yet.