Answered Using In-built functions in FOR EACH

[Stefio Almeida]

Hi
I am writing a piece of code that will run on PROGRESS V9 to OpenEdge 10.x and my question is as stated in the subject, are using functions like CAN-DO, LOOK-UP in the FOR EACH selection criteria an efficient way of writing code?
Example, is the below code an efficient way of fetching records? or i should fetch the records anyways and then do the necessary checks and skip withing the body of FOR EACH loop?

for each ld_det where CAN-DO (lv_status, ld_status)
                            and ld_site = lv_site
                            and CAN-DO (lv_loc, ld_loc)
                      no-lock:
end.

Thanks in advance for any inputs.

Regards,
Stefio

 

[Cringer]

Do not use CAN-DO! It isn't designed for what you are using it for. LOOKUP is what you are after, but having any sort of function on the right hand side of a query is asking for trouble because the ABL can't evaluate the indexes at compile time. You'll likely end up with table scans. The best way to do your request is to build a temp table with the options in that you need and join.

for each ld_det no-lock where ld_site eq lv-site,
  first tt-status where tt-status.status eq ld_status,
  first tt-loc where tt-loc.loc eq ld_loc:
end.

Something along those lines should work, although make sure you have a good index on ld_site or else you'll get the table scans anyway.

 

[Stefio Almeida]

Do not use CAN-DO! It isn't designed for what you are using it for. LOOKUP is what you are after, but having any sort of function on the right hand side of a query is asking for trouble because the ABL can't evaluate the indexes at compile time. You'll likely end up with table scans. The best way to do your request is to build a temp table with the options in that you need and join.

for each ld_det no-lock where ld_site eq lv-site,
  first tt-status where tt-status.status eq ld_status,
  first tt-loc where tt-loc.loc eq ld_loc:
end.

Something along those lines should work, although make sure you have a good index on ld_site or else you'll get the table scans anyway.

Thanks Cringer for the information. Just to make sure i follow correctly on the last part, when you say table scans , you mean the entire table is scanned and table index are not utilized?

 

[Cringer]

Yep.

 

[TomBascom]

It's actually worse than Cringer indicates.

1) CAN-DO() cannot be evaluated on the server side. Thus *all* of the records *must* be sent to the client to be evaluated for selection. Yes, you will find a lot of legacy code that uses CAN-DO(). And many people have picked up the habit. But it is a very, very bad habit that needs to be stamped out. FWIW the root of the practice goes way back in time to a time before the LOOKUP() and INDEX() functions existed (this was the 1980s...). Someone noticed that the security function CAN-DO() could be subverted to meet those needs. At the time there was no client/server either. So the performance downside was much less apparent. But there are other reasons to not use CAN-DO() for strings. Because its intended use is security magic characters in the arguments will change its behavior. And if you are running as root (a bad idea, but people do that too) it always returns "yes" (because root can do anything....)

2) Up until very recently (11.something) all 4GL queries, even table scans, always use an index. So there is always IO associated with both data records and the index scan. There is some new OE11 4GL syntax that allows a deliberate unordered 4GL table scan. It is something like "FOR EACH customer NO-INDEX" but don't quote me -- I'm not looking at the docs right now.

 

[Cringer]

The keyword is TABLE-SCAN. Very useful. Although it doesn't allow you to use it with OPEN QUERY or dynamic queries which is a shame.

 

[Stefio Almeida]

Thanks Cringer and Tom. I was advised too not to use the functions but was not explained exactly why. Its clear to me now. Thanks for your time and attention.

Regards,
Stefio