Posts Tagged ‘Query plan’

Query Processing and Abstract plan of stored procedure

July 22nd, 2012 No comments

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

Query processing and Abstract Plans

July 22nd, 2012 No comments

Query –> What are the 6 modules of query processor?

Answer –>

1. Parser

2. Normalization

3. Preprocessor

4. Optimizer

5. Code generator

6. (1) Procedural execution engine

6. (2) Query execution engine

Query   –> At which level in Query Processor Modules we can determine if the statement may benefit from using the statement cache?

Answer  –> Level 2 (Normalization)


Query  –> What does Normalization infers while query parsing (Query Processor Modules) ?

Answer  –> Normalization involves determining column and table names, transforming the query tree into conjugate normal form (CNF), and resolving datatypes.

Query –> Define “Query Plan” ?

Answer –> Query Plan consists of  retrieval tactics and an ordered set of execution steps, which retrieve the data needed by the query.

Courtesy: sybooks

Categories: ASE Tags: , , ,