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

asked 07 Dec '11, 09:50

Justin%20Willey's gravatar image

Justin Willey
7.0k116149220
accept rate: 21%

edited 08 Dec '11, 07:40

Volker%20Barth's gravatar image

Volker Barth
31.5k321462677


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:)

permanent link

answered 07 Dec '11, 10:11

Volker%20Barth's gravatar image

Volker Barth
31.5k321462677
accept rate: 32%

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.

(07 Dec '11, 10:15) Justin Willey
Replies hidden
1

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.

(07 Dec '11, 10:27) Volker Barth

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.

permanent link

answered 11 Dec '11, 14:51

Breck%20Carter's gravatar image

Breck Carter
27.0k446617889
accept rate: 21%

edited 11 Dec '11, 14:53

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

(11 Dec '11, 15:10) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×114
×106
×27
×4

question asked: 07 Dec '11, 09:50

question was seen: 1,012 times

last updated: 11 Dec '11, 15:10