Posted on 10-Aug-2018 19:37:35
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, dont 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.
PS: I wrote this in Jan 2011
Vishnu Vardhan Chikoti is a co-author for the book "Hands-on Site Reliability Engineering". He is a technology leader with diverse experience in the areas of Application and Database design and development, Micro-services & Micro-frontends, DevOps, Site Reliability Engineering and Machine Learning.