Benefits of using FIND FIRST over FOR FIRST and FOR FIRST over FIND FIRST ?

Hi,

Could anyone please tell me benefits of using FIND FIRST over FOR FIRST and FOR FIRST over FIND FIRST in terms of performance and extra options they have ?

Your answers will be highly appreciated :)


Thanks & Regards,
SaiChandP.
 

TomBascom

Curmudgeon
FOR FIRST does not do what you think it does.

Specifically it does NOT return the first record of the result set specified by the WHERE clause as ordered via any BY criteria.

What it does do: return a single record based on the selection criteria without regard to sorting criteria. SELECTION, then sorting. Since one record is selected there is nothing to sort.

So "FIRST" is only ever coincidentally correct. Here is a simple example showing how you can be burned by this:

Code:
for FIRST customer no-lock by discount:
  display custNum name discount with no-box.
end.

Cust Num Name                           Discount
──────── ────────────────────────────── ────────
       1 Lift Tours                          35%

for EACH customer no-lock by discount:
  display custNum name discount with no-box.
end.

Cust Num Name                           Discount
──────── ────────────────────────────── ────────
      41 On Target Rifles                     0%
    1066 Runners Anonymous                    0%
      56 Super Golf Center                    0%
    1005 Mike's Sports                        0%
    1040 Surf and Sport World                 0%
       6 Fanatical Athletes                   0%
    1020 Abc Sports                           0%
    1025 Athlete's Track                      0%
      51 Butternut Squash Inc                 0%
      27 Bumm Bumm Tennis                     0%
    1030 Soccer Universe                      0%
    1035 Sports Usa                           0%
    1045 Play Sports                          0%
    1731 K & K Sports                         5%
    1722 Barry's Up North                     5%
    1732 Play It Again Sports                 5%
    1240 Ron's Sports World                   5%
. . .

Even if YOU know this and code defensively the poor maintenance programmer who comes along 3 years from now is extremely unlikely to understand the quirks of FOR FIRST. It is far, far better to never use it. In fact it is a good idea for your QA and build processes to scan the code for FOR FIRST and flag any usage as a defect to be corrected. SonarQube, https://riverside-software.fr/ can help you with that.
 
Last edited:

TomBascom

Curmudgeon
FIND FIRST has a very unfortunate mythology grown up around it which claims that it is "faster" than plain old FIND because it, supposedly, eliminates an extra internal operation to see if the record is really unique.

This mythology is false.

Code:
FIND customer WHERE custNum = 13.
does NOT look to see if there is more than one custNUm 13. There is a unique index on custNum and the database *knows* that that is a unique find. No extra work is performed and adding FIRST has zero impact on the performance of the statement.

If there is zero impact there is no harm, right?

Wrong.

Because of this myth certain well-known applications have promulgated a policy of always adding FIRST whenever someone types FIND. As a result cases where the FIND is NOT unique now "work". Instead of getting a runtime error when ambiguous criteria are specified the code "just works". Except that now it is working wrong (this is often known as a "bug"). Where you should have had a FOR EACH processing a whole set of records you are only processing a single record. This will often sneak past QA because test systems don't have real data. It may even be unnoticed in production for a very long time. Until a user adds that second or third control record and only the children of the first are being updated (this is just one example, there are an infinite number of ways that specious FIRST keywords can go wrong).

To make it even more fun different WHERE clauses can result in different supposedly FIRST records.

In defense of the "it's faster" myth it is true that sometimes FIRST does make FIND faster. This happens when the WHERE clause is not unique and there are many records in the result set. In other words you found the wrong record really fast. Congratulations.

You could, perhaps, argue that a FIND FIRST prior to entering a loop which iterates over the result set with FIND NEXT is a reasonable usage. And that isn't completely wrong but, in such a case why aren't you using FOR EACH?

FOR EACH is much more powerful and efficient. It supports the use of multiple indexes to resolve the WHERE clause along with explicit sort criteria in the BY clause and in a client/server environment it vastly reduces network traffic.

The FIND statement is a wonderful statement when used to find a single unique record. In which case there is no advantage to slapping a FIRST into the statement. If you are not looking for a single unique record then FIND is almost always the wrong statement to be using and FIND FIRST is compounding that error.
 
Last edited:

tamhas

ProgressTalk.com Sponsor
Have you considered doing write-ups like this and posting them on wss.com so that you just need to drop the URL to respond thoroughly?
 
Hello Tom, thanks for sharing details regarding For First/Each.

I still have doubt that do we ever need client side sorting (By) with For First. AFAIK, For First just picks the first record as per where clause and take that in record buffer. It doesn't take multiple records in record buffer and then sort it with By clause. If we do like below then first record of For Each and For First will be the same.

Code:
for each customer no-lock :
  display custNum name discount with no-box.
end.

Code:
for first customer no-lock :
  display custNum name discount with no-box.
end.

Thanks and Regards,
Learner
 

TomBascom

Curmudgeon
That is my point. Mostly.

IF someone specifies a BY clause and the BY criteria does not happen to match the selected index then the FIRST record will only accidentally be the record that they expect. That accidental correctness is also much more likely to happen in development and test systems where there is less data and the data that does exist is usually cherry-picked to work correctly.

Expecting a programmer to know that the language will not do what they are very clearly asking it to do is a recipe for buggy code. Deliberately encouraging programmers to use statements that are known to work in counter-intuitive ways is just asking for trouble.
 

tamhas

ProgressTalk.com Sponsor
Quite aside from the vagaries of sort order and the like, it is almost certain that treating the first record of a group as special is a violation of good relational design. It is certainly reasonable to do a CAN-FIND test to determine whether or not there is one or more records matching certain criteria, but writing something to that record is just plain wrong.
 
Top