(originally published at www.sybase.com in July 2009; moved to SCN in December 2012)
In ASE 15.0.2, a feature called 'Deferred Compilation' was introduced to fix some classic issues around query optimization in ASE stored procedures. Anything that makes life easier is appreciated, and Deferred Compilation is indeed a Good Thing. However, these's a not-so-obvious aspect to it that you should know about, or you may find yourself banging your head against the wall -- that's what happened to me, at least, so I figure it may be useful to share my experience about this.
But first, let's look at what Deferred Compilation does. The classic query optimizer issues I mentioned above are in fact two specific problem cases whereby it is not uncommon to see the optimizer pick a sub-optimal query plan. The first case is when a where-clause has a filter predicate like "where mycolumn = @Var, with @Var being a local variable:
create proc p1 as
declare @var int
select @var = max(col) from yourtab
-- this is the problem statement:
select * from mytab
where mycolumn = @var
go
The other case is about a #temp table that is created inside a procedure and populated with data, and then used in queries:
create proc p2 as
select * into #t from mytab
-- this is the problem statement:
select * from #t, yourtab
where col1 = col2
go The classic problem with these queries, which Deferred Compilation tries to solve, is that the ASE query optimizer doesn't have enough information to generate an optimal query plan. Query plans are generated for all statements in the stored procedure when the stored procedure is executed for the first time, just before execution of the first statement starts. However, at that point, the value of the local variable @Var is unknown since it is the result of statements that have not yet been executed (the select max(col) from yourtab), so it's not possible to predict what the value of @Var will be. In this case, the ASE query optimizer cannot determine the selectivity of this search argument, so it just assumes some 'magic number' (like 25%), which is unlikely to be correct. Likewise, in the second example, it is not known how many rows the table #t will contain, and how large the table will be, since it has not been created yet. Consequently, the ASE optimizer just assumes it will contain 100 rows and require 10 data pages of storage space -- which, again, is unlikely to be correct.
As a result of these assumptions, the query plans for these statements may not be as good as they could have been, for example because an existing index on 'mycolumn' is not being used, or because the join order of #t and yourtab ends up the wrong way around.
It is in these cases where Deferred Compilation applies: for the statements in a stored procedure involving a where-clause with a local variable or a #temp table create inside the procedure itself, generating the query plans for these statements is postponed until execution actually reaches these statements. At that point the value of @Var, and size and row count of #t, are known, so the optimizer has accurate information to generate query plan. You can actually see this effect with set showplan on: when the procedure gets executed for the first time, no query will be shown for the statements where deferred compilation applies. Once execution reaches those statements, the showplan info is printed. As of 15.0.3 ESD#1, the showplan also contains the messages "To be Optimized at runtime using Deferred Compilation." and "Optimized at runtime using Deferred Compilation." at those points.
Note that once the query plan has been generated, it will be re-used for subsequent executions of the statement, even though the value of @Var, or the size of table #t, could be different than when the plan was originally generated. If it is desired to apply Deferred Compilation prior to every execution of the procedure, you should create or execute the procedure 'with recompile'.
As of 15.0.2, Deferred Compilation is enabled by default, and until 15.0.3 ESD#1, it could only be switched off by enabling traceflag 7730 (applies server-wide). As of 15.0.3 ESD#1, the feature is controlled by the new configuration parameter procedure deferred compilation, which is enabled by default. The experience so far is that Deferred Compilation tends to improve query performance, so it is recommended to leave it enabled unless you find very specific reasons to switch it off.
Just note that when using Compatibility Mode (see the whitepaper at http://www.sybase.com/detail?id=1063556), Deferred Compilation will never be used since Compatibility Mode triesd to mimic the 12.5 optimizer behaviour, where there was no such thing as Deferred Compilation.
So, basically, Deferred Compilation is great. Now let's look at the part that may drive you mad (as it did to me). Currently, Deferred Compilation applies only to stored procedures. So while you could have exactly the same query in a SQL batch, deferred compilation would not apply there.
In addition, Deferred Compilation applies to select, update and delete statements, but not to select-into statements, even in a stored procedure. These limitations may be lifted in some future ASE release, but for the time being you should be aware of them.
So, how can this great feature be a problem?
Well, imagine you've identified a statement in some large stored procedure with a query plan that looks unreasonably slow. To isolate the problem, typically you'd extract the SQL for that statement, and run it in isolation in ISQL as a batch query. Now, if Deferred Compilation applied inside the stored proc and it affected the final query plan, then it won't apply when running that statement in ISQL. Consequently, you get a different plan when running the statement in isolation compared with running it as part of a procedure, under otherwise identical circumstances. The reason why this is so deceptive that as long as ASE exists, this difference would not have occurred. So when this happened to me, it was highly confusing; fortunately, while double-checking everything for the Nth time, one of the optimizer engineers helpfully pointed out I was chasing the wrong problem.
Of course, the solution is simple: just run that single statement in a small procedure of its own.
As for the select-into, this can come back to haunt you when working on a select query that produces a large result set. Typically, the first thing you'd do is to put in an 'into' clause so as to get the result set out of the way -- it's a waste of time to watch those 100,000 rows stream over your screen every time you run that query. But alas, while Deferred Compilation may apply to a select statement, it won't apply to an otherwise identical select-into statement, possibly leading to a different query plan. I also had to find this out the hard way -- with hindsight, I guess I should have read the documentation!
