|
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 |
|
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:) 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:
For the case expression:
(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. Thanks, Breck, that's the doc page I have tried to find...
(11 Dec '11, 15:10)
Volker Barth
|
