|
In the version of Sybase SA 12.0.1.3942 and above was an error of the query, which was formally the fix in SA 12.0.1.4085. The initial description of this problems, see the topic Sybase SA12, 16: Error query optimization with UDF. It seems that the correction of this error is non-inline query optimization with UDF with "with". SQL-code tables, UDF, the request is in zip-files (with the execution plan of the query). File "plan_sa_12.0.1.3924.zip" - is a query execution plan in SA 12.0.1.3924, in it as you see there is an execution plan for "Main Query" and "SubQ1" (an execution plan for dba.FTest1). File "plan_sa_12.0.1.4085.zip" - is a query execution plan in SA 12.0.1.4085, in it as you see there is only the execution plan for "Main Query", but there is no implementation plan for dba.FTest1 ("SubQ1"). For comparison, add another file plan_sa_12.0.1.3924.NOT_DETERMINISTIC.zip - this query execution plan in SA 12.0.1.3924 on condition that dba.FTest1 enabled option "NOT DETERMINISTIC". In it you see there is only the execution plan for "Main Query", but there is no implementation plan for dba.FTest1 ("SubQ1"). If we compare the running of the query plans plan_sa_12.0.1.3924.NOT_DETERMINISTIC.zip and plan_sa_12.0.1.4085.zip we see that they are almost identical. The impression is that the correction of this error in 12.0.1.4085 for similar UDF simply substitute option "NOT DETERMINISTIC" real and inline-optimization occurs. |
|
After revisiting my bypass optimiztion statement and the original issue reported in the previous thread, the reason there is no in-lining in this case is directly due to the fix to the original problem. http://search.sybase.com/kbx/changerequests?bug_id=751771 It is now determined that it was incorrect to inline this function because it has a recursive common table expression and inlining of such UDFs is now disabled for queries with common table expressions. |
|
What is your concern with this Stalker? All plans do complete in under 20 µsecs. The difference is mostly associated with the fact that for 2 of those plans, the optimization method was "Bypassed heuristic" which treats the function as a simple expression. Given the optimization time is a whopping 679.14µsecs it seems to be a reasonable trade-off to bypass optimization. And as a trade-off I would suggest it was a pretty good one given the net gain in the resulting times:
My concern is that when the query select dba.FTest1 (TEST1.ID1) from dba.TEST1 in SA 12.0.1.3924 (and younger) I see in the "Plan Viewer" main query execution plan (Main Query) and the plan of the subquery "SubQ1" - the query plan when the dba.FTest1. And when the same query in SA 12.0.1.4085, I see "Plan Viewer" only the main query execution plan (Main Query) and did not see the plan of the subquery "SubQ1" - the query plan when the dba.FTest1. Q: How much in 12.0.1.4085 in "Plan Viewer" does not show the execution plan request dba.FTest1 (SubQ1) ?
(15 May '14, 07:45)
Stalker
1
As far as the non-deterministic and 4085 plans you provided are concerned FTest1() is just a function and can be treated as an expression. It is no different from an expression or a built-in function in those plans. There was no in-lining done (because optimization was bypassed) and so no parsing, annotating, optimization, ... of the query inside the function was done as part of this outer plan. That is why you don't see a SubQ1 sub-plan. Access plans will be generated for the various statements inside of the UDF FTest1 (when and as required) but those are not determined until later; as part of first call to the function for example. The timing of that call happens as part of the execution phase of the 'outer' plan so any such additional plan 'pieces' required for the entire execution of the outer query do show up in the plan of the outer query. A SubQ1 sub-plan may show up again in other plans when you have different starting condtions (different data distributions for example). But in this case there was not benefit in chasing a more optimized solution so it doesn't happen here.
(15 May '14, 09:08)
Nick Elson S...
|