CAN-DO

[Rob Fitzpatrick]

Interesting. I understand that if you did something silly like increment a loop counter and use it in a function in the WHERE clause it would have to be evaluated with every iteration. But a function like I've shown above obviously (to me, anyway) doesn't change its value based on anything the FOR block does. I would have thought the runtime could just evaluate for the first loop iteration and treat it as a constant for the rest.

 

[Cringer]

Wouldn't be too hard to test it, but I'm not surprised. A DO lv-i = 1 to NUM-ENTRIES() loop evaluates the NUM-ENTRIES every time even though it's constant.

 

[TheMadDBA]

Sorry... using shorthand was lazy of me there. When I say functions in a where clause I specifically mean those that refer back to the table.

/*--- ok ---*/
for each customer where customer.cust-num = integer("1") no-lock:
end.

/*--- not ok ----*/
for each customer where string(customer.cust-num) = "1" no-lock:
end.

If Progress can determine the value before reading any records you are usually ok (assuming the index is proper), if it needs the table to execute the function then you can't use an index bracket and reads go up.

Same issue with IF logic in the WHERE...

/*--- ok ---*/
for each customer where customer.cust-num = (IF vFlag then 1 else 2) no-lock:
end.

/*--- not ok ---*/
for each customer where customer.cust-num = (IF vFlag then 1 else customer.cust-num) no-lock:
end.

As far as executing the function over and over again... that is a little fuzzy. There are actually flags to turn off being able to compile code with user defined functions and methods because it only executes those once. For built in functions it runs those for every record if you are referencing the table, but only once if you aren't.

See http://knowledgebase.progress.com/articles/Article/P77953 for more info.

 

[TomBascom]

Consider the following:

for each customer no-lock where custNum = random( 1, 10 ):
  display custNum name.
end.

 

[TomBascom]

That form of IF is considered a "function". At least that's what the docs call it...

Said docs include this "gem"

FOR EACH Customer NO-LOCK BY IF Customer.Balance > 10000 THEN 1  ELSE (IF Customer.Balance > 1000 THEN 2 ELSE 3) BY Customer.SalesRep:
   DISPLAY Customer.SalesRep Customer.Balance Customer.Name.
END.

Which goes to show that just because you /can/ do something doesn't mean that you /should/ do something.

 

[TheMadDBA]

lol.. that random is a good example to show that it only gets executed once... change it like this and it gets executed many times (without index benefits)

for each customer no-lock where
              customer.cust-Num = random(customer.cust-num,customer.cust-num + 10):
  display cust-num name.
 end.

Sometimes it is just easier to tell developers to avoid the use of IF and functions in the where and by clauses. In certain cases you will be ok, but you are really asking for trouble most of the time.

 

[TomBascom]

Just in case people wonder why I get so cranky about some of this stuff...

 

[TheMadDBA]

I have found a few lately that are really awful... every time you think it can't get any worse you kick another rock and find a hidden gem. Both of these are for tables with millions of rows.

FOR EACH Table NO-LOCK WHERE
(IF ipDateType = 1 THEN Table.date1 >= ipBeginDate 
 ELSE IF ipDateType = 2 THEN Table.date2 >= ipBeginDate 
 ELSE IF ipDateType = 3 THEN Table.chardtstamp GE ipStampBegin                
 ELSE Table.date1 >= ipBeginDate )                                            
 AND
(IF ipDateType = 1 THEN Table.date1 <= ipEndDate 
 ELSE IF ipDateType = 2 THEN Table.date2 <= ipEndDate                          
 ELSE IF ipDateType = 3 THEN Table.chardtstamp LE ipStampEnd
 ELSE Table.date1 <= ipEndDate)                                                
  AND
(IF ipTradeType = 2 THEN Table.char_field = "S"
 ELSE IF ipTradeType = 3 THEN Table.char_field = "F"
 ELSE Table.char_field = Table.char_field)
  USE-INDEX char_field_index:

Also this one....

for each table where string(year(table.date1),"9999") +
                                  string(month(table.date1),"99") +
                                  string(day(table.date1),"99") = substring(ipStampBegin,1,8) exclusive-lock:
end.

 

[TomBascom]

That stuff is probably really fast in a DEV environment with a 5MB database...

 

[TheMadDBA]

The DEV/UT environments are full copies from production... they just say the DEV hardware is slow because the second time they run it... it gets faster

 

[Cringer]

Hehe that's hilarious!
We've got that sort of thing all over the shop. Just changed a screen that had an Open Query statement for a filtered browse to a dynamic query. Ifs and buts all over! Unless you provided one filter it was always a full table read and took about a minute to return. An hour's work (including testing) later and it's much easier to maintain, and only reads as many records as it actually displays no matter what filters are used and returns the results with no visible delays (unless you do something silly with the filters of course). Suddenly the screen (and the business function behind it) goes from a screen nobody uses, and therefore the rather critical business function is ignored) to one that can be used easily, and I'm a hero. So I'm not complaining too loudly...

 

[andre42]

Also it looks like they added @ to the list of special characters in V11 (for the domains)

http://knowledgebase.progress.com/articles/Article/000041404

Here is the (hopefully current) complete list of special characters: http://knowledgebase.progress.com/articles/Article/000039598
@ . * ! , ~ #
At least * and . are the same as in the matches operator.

Regarding root: I suppose that means if the Progress _User is named root and you call can-do without a second parameter then the condition will be true, regardless if "root" is actually contained in the list.

 

[TheMadDBA]

Even when your userid is root it doesn't work that way. At least not in V10 and V11. I am pretty sure it didn't work like that in V9 as well. It is possible it worked like this in V8 or prior, but I think this is a documentation flaw at this point.

A little bird told me that Progress has added a startup parameter in 11.5 to make the @ behave like it did pre multi-tentant. I guess we will find out for sure when it ships.

 

[Stefan]

No, (user defined) functions are only evalutated once:

function foo returns logical (
   i_i as int
):

    message 'foo' view-as alert-box.
  
    return i_i > 2.
  
end function.   

define temp-table tt
   field ii as int
   .
  
  
create tt. tt.ii = 1. 
create tt. tt.ii = 2.
create tt. tt.ii = 3.


for each tt where foo( tt.ii ):

   message 'bar' tt.ii view-as alert-box.

end.

Edit: Sorry - just fell into this thread halfway thru - the above has already been answered.