Optimization in functions and procedures

[justin_willey]

v10.0.1 various (recent) builds

In a user defined function which we have been working on to improve performance we had a construction like this:

    if @OverRideDate is not null and @WeekNo >= WeekContaining(@OverRideDate) then
       if @OverRide = 3 then return 0
       elseif @OverRide = 2 then return 15
       end if
 end if;

I had assumed that because @OverRideDate was a locally declared (date) variable, the second part of the condition with the user-defined function WeekContaining() would not be evaluated if @OverRideDate was null. WeekContaining() is a function requiring access to database tables etc so I didn't want it called unless necessary.

Running the function in the de-bug suggested that WeekContaining() was being invoked whatever the value of @OverRideDate was, null or not.

Rewriting the statement with nested IFs:

     if @OverRideDate is not null then 
       if @WeekNo >= WeekContaining(@OverRideDate) then
            if @OverRide = 3 then return 0
            elseif @OverRide = 2 then return 15
          end if
     end if
  end if

avoided unnecessary calls to WeekContaining() and went far faster.

Does the optimizer normally spot things like this, or is it something one should pay careful attention to?

Thanks

[VolkerBarth · Accepted solution]

Obviously, I can't tell how smart the optimizer is in such situations.

However, I would rewrite the statement with nested IFs simply because I would try to not rely on the smartness of the engine, and it's documented that SQL does not use "short-circuit evaluation" (in constrast to PLs like C/C++, which by design evaluate their conjuntions from left to right), so it's free to evaluate both parts of the ANDed expression in any order it likes. (IIRC, there are several topics on this here, too, but a quick search wasn't too successful...)

With the nested if statement approach (or an if expression) you can assure that an expensive operation will only be evaluated when it's necessary from your point of view. I think that's smarter:)

[justin_willey · Accepted solution]

Thanks Volker - I'd not found that phrase - "SQL does NOT use short-circuit evaluation" - it's the key isn't it, and explains the behaviour. I'll need to watch out for similar situations.

[VolkerBarth · Accepted solution]

A short search in the docs wasn't too helpful, either - one of the rare references related to the evaluation of operands is with the if expression and the case expression ... Time to send a note to DCX?

For the if expression:

IF condition THEN expression1 [ ELSE expression2 ] { ENDIF | END IF }

expression1 is evaluated only if condition is TRUE. Similarly, expression2 is evaluated only if condition is FALSE.

For the case expression:

With SQL Anywhere, expression evaluation occurs when each WHEN clause is evaluated, in their syntactic order, with the exception of constant values that can be determined at compile time.

[Breck_Carter]

To expand on Volker's answer...

It's not easy to find the "no short-circuit evaluation" statement, but here it is, expressed not as a limitation but as a feature: http://dcx.sybase.com/index.html#1201/en/dbusage/semantic-queryoptimization.html

Example

Unlike the SQL language definition, some languages mandate strict behavior for AND and OR operations. Some guarantee that the condition on the left-hand side will be evaluated first. If the truth of the entire condition can then be determined, the compiler guarantees that the condition on the right-hand side will not be evaluated.

This arrangement lets you combine conditions that would otherwise require two nested IF statements into one. For example, in C you can test whether a pointer is NULL before you use it as follows. The nested conditions in the first statement can be replaced using the syntax shown in the second statement below:

if ( X != NULL ) {
   if ( X->var != 0 ) {
      ... statements ...
   }
}

if ( X != NULL && X->var != 0 ) {
      ... statements ...
}

Unlike C, SQL has no such rules concerning execution order. SQL Anywhere is free to rearrange the order of such conditions as it sees fit. The original and reordered forms are semantically equivalent because the SQL language specification makes no distinction between one order or another. In particular, a query optimizer is completely free to reorder predicates in a WHERE, HAVING, or ON clause.

[VolkerBarth]

Thanks, Breck, that's the doc page I have tried to find...