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.

Subscribe Us

Leave Blank:Do Not Change:

Your email:

Worldwide Visitors

Contributors

Anurag has more than 6+ years of experience in Sybase Database Development .His Area of expertise includes Performance, Query Optimization, Cost Optimization, TSQL Development. He is also involved in Consultancy to Financial Firms for Database Implementation and Maintenance. He has supported many Global Financial firms and recently started a new portal -Mati Rang. AnVa (Founder) has more than 6+ years Exp in Sybase ASE/REP Database Administration. His area of interest is ASE Implemenation and maintenance , Performace Tunning , Sybase HA ,Shared Cluster and Replication. Also exploring the In-Memory Databases (HANA) , Big Data, Hadoop and Java. He is also supporting the Forum Sybase Team and started this sybaseblog.com in late 2009.Andrew is Guest Blogger on sybaseblog.com. Andrew have been working with Sybase for more than a decade: ASE & RS mostly.
He has written lot of tools that help to manage/monitor the system for ASE and Replication and some tools are on the way..
He is also poet and love to be creative all the way.

Admin

Disclaimer

This Blog is neither sponsored by nor in any way affiliated with SAP & Sybase,Inc. In any case, neither any authors nor SAP & Sybase Inc assumes any responsibility for errors in the blog postings, nor do they have any liblilties for damages resulting from the use of blog postings.If you are using any Supported & Unsupported commands/features listed in blog, this is completely on your(user) risk, sybaseblog.com not having any libilitiy.

This blog is only for sharing purpose. The opinions expressed here represent bloggers own and not those of their any past and present employer.