Search code examples
openedgeprogress-4gl

When to use FIND FIRST and when to use FOR FIRST

My company has a lot of codes using FIND FIRST to get the first register (Searching on a UNIQUE Field).

But while learning more about Progress, several places recomends me to search with FOR FIRST instead, by saying that FIND FIRST may not be finding the first register with the same value.

By saying that I need to find the first value using a unique field, does it really make a difference between these two? Besides not needing to check if the field is available

FOR FIRST file_path WHERE file_path.file_name = "ABCDEF" NO-LOCK:
    // Do my thing
END.

FIND FIRST file_path WHERE file_path.file_name = "ABCDEF" NO-LOCK NO-ERROR.

IF AVAIL file_path THEN DO:
    // Do my thing
END.

The only issue that I found by using FOR FIRST is not being to use IF NOT AVAIL in case of no records are available.

Solution

  • Is your target record UNIQUE?

    In other words - does the WHERE clause result in the use of an unique index?

    If it is then you should not be using FIRST. Neither FIND FIRST nor FOR FIRST is helpful in such a scenario. Both constructs will "work" but they will give the maintenance programmer (who might be you 6 moths from now...) the idea that there is a multi-record result set possible and that you are sorting it for some reason by using FIRST.

    FIND is designed and intended to be used to retrieve a single record. If you only ever expect one record then you should not be adding FIRST every time that you type FIND. (Yes, I know that there is a very large body of code from certain well-known vendors that break that rule. You still shouldn't do it.)

    In a very few cases there is a natural and reasonable ordering to a set and FIRST might make sense. Date fields for example are sometimes an exception (and in those cases the FIND is not unique).

    You may hear some myths about "FIND FIRST is faster". This is not true. A unique FIND executes exactly the same code path as a FIND FIRST and they are not at all different from a performance perspective. This is easy to test and verify. In any case where FIND FIRST is "faster" you are not doing a unique find and unless it is one of the rare cases where that is appropriate you are potentially introducing a bug by making that "first" record special (a violation of 3rd normal form) or covering up for the lack of an appropriate index. You are, basically, "finding the wrong record faster".

    FOR FIRST is a really, really bad idea for the reasons above and is even more deceptive because FOR supports an explicit BY clause for sorting and, so, you might think that you are getting the FIRST record in a sort order that you specify with BY.

    Sadly that it not so. The rule is "selection, then sorting". So FOR FIRST will select exactly one row. Based on whatever index the WHERE clause picked uninfluenced by the BY (except in the rare case that two or more indexes are tied for appropriateness in the index selection rules). And then sort that one row. Sorting one row results in the same one row.

    Your particular question about not being able to check AVAILABLE when you use FOR FIRST could be recast as an interesting approach. If you don't allow FIRST to muddy the water then you could say that using FOR is cleaner in some ways. The traditional approach to finding data often looks something like this

    find customer no-lock where custNum = 2 no-error.
if available customer then
  display name.
 else
  display "oops".

    And in all too many cases the code ignores the possibility of an ELSE.

    If you really only want to follow the happy path and do not need to handle the NOT AVAILABLE situation then this:

    for customer no-lock where custNum = 1:
  display name.
end.

    might be something to consider. (Notice that there is no FIRST to confuse people with.)

    As for:

    ... several places recomends me to search with FOR FIRST instead, by saying that FIND FIRST may not be finding the first register with the same value.

    I don't know where you are seeing such advice but it is either misunderstood or simply wrong.

    So, to sum it up... if you are peppering your code with FIRST every time you write FIND, or if you use FOR FIRST at all, that is a RED FLAG that you are not thinking clearly about how that code actually works.