[RBASE-L] - Re: Paren's Around VAR's in Comparison and Performance Impact

[Bill Downall]

On 20 Aug 2004 at 16:41, J. Stephen Wills wrote:

> UPDATE MY_TABLE +
> SET +
>    CurrentCT = (.vCurrentCT), +
>    JobComplete = (1) +
> WHERE +
>    ID = (.vID) +
> AND TimePeriod = (.vTimePeriod) +
> AND  vProjectID = (.vProjectID)

Steve,

Not a function, but an R:Base expression. You are forcing R:Base to never use an 
index, because it assumes it might have to re-evaluate that expression for every row 
it checks.

Much much faster might be:

UPDATE MY_TABLE +
SET +
   CurrentCT = (.vCurrentCT), +
   JobComplete = (1) +
WHERE +
   ID = .vID +
AND TimePeriod = .vTimePeriod +
AND  vProjectID = .vProjectID

Another way, that gives you a little control over the order in which indexes are used, 
is to not use parentheses around the VALUE to the right of the operator, but around 
the whole CONDITION (column operator and value).

WHERE +
  ( ID = .vID) +
AND (TimePeriod = .vTimePeriod) +
AND  (vProjectID = .vProjectID)

or:

WHERE +
  ( ID = .vID) +
AND ((TimePeriod = .vTimePeriod) +
AND  (vProjectID = .vProjectID))

Bill