Sybase 15: Deferred Compilation

Deferred compilation is a real good feature introduced in Sybase 15.0.2 which solves the problem with the use of local variables and temp tables inside stored procedures. If you are not aware of what the problem is, here is what it is.
When you execute a procedure for the first time, the optimizer generates the query plans for all the queries in the procedure at a go before execution. As the plan is generated before execution of the statements, when the optimizer hits a query using a local variable or locally created temp table, there is no way for the optimizer to know how many rows the temp table has and also what value a local variable holds. So the optimizer assumes some magic numbers in generating the plan. The assumption of the optimizer can go wrong completely and the query can perform badly.

To avoid this issue, we either used to create the temp table and its index(es) in a wrapper procedure and then call a sub-procedure which actually uses the temp table or we used to go with our forcing and hinting options.
Now in 15.0.2, with deferred compilation, the generation of query plan for queries containing temp tables and local variables are “deferred”. That means, the query plan is generated after execution of the statements that create and populate temp table, create index on the temp table or assign the value to the local variable. This will let the optimizer generate the appropriate plan.
Below lines in the “showplan” of a stored procedure tell that the optimization of the query was deferred.
 
To be Optimized at runtime using Deferred Compilation.
Optimized at runtime using Deferred Compilation.
Few things to remember here are
(a) Deferred compilation works inside the procedure. Now, don’t take out queries from the procedure and run them manually to see deferred compilation.
(b) Somehow, it is not working when we do a “SELECT INTO”. We have to replace these with “INSERT INTO” for them to work.
(c) There is a system level config parameter to turn this on/off. Run sp_config “deferred” and you will see it. This feature is turned on by default.
(d) Now that the optimizer generates the plan based on actual data in temp tables and the fact that the plan for the first execution gets stored and reused, be careful with procedures where the temp table data changes drastically between executions. You may have to use “WITH RECOMPILE” option for them (I haven’t seen a SET command to turn this on/off at a session level).
We have tried to use this feature on our new Sybase 15.0.3 environment and it is very helpful.
About the Author
The author of the blog is a cross-functional leader and part of the Core team at Tectoro Consulting, India. He currently works on multi-market multi-asset enhancements to Gloss for our client in Hyderabad. His prior expertise is in analysis, design and development for Derivatives Regulations – Dodd Frank, EMIR, MiFID, JFSA, etc across product/entity/counterparty scope, entity registration, mandatory clearing and  transaction reporting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s