"FOR FIRST" or "FIND FIRST"

Would like to know which of the following is better in terms of performance?

FOR FIRST cus where cus.cus-code = 123 exclusive-lock:
cus.credit = 0.
END.

or

FIND FIRST cus where cus.cus-code = 123 exclusive-lock no-error.
if available cus then
cus.credit = 0.

Thanks
Joel
 

mosfin

Member
the FIND command is better, since it has NO-ERROR and NO-WAIT options,
i.e: it allows you can catch & handle locked record :
FIND FIRST cus WHERE cus.cus-code = 123 EXCLUSIVE-LOCK
NO-ERROR NO-WAIT.
IF NOT AVAIL cus THEN
IF LOCKED cus THEN message "Record locked by a user".
ELSE message "Record not found".
ELSE cus.credit = 0.

with
FOR FIRST ... EXCLUSIVE-LOCK
if the record is locked by another user, you will get error message like "Record locked..retrying.."
(i don't remember exactly the time-out period) but the pogram will abort if the user does not release the record
within that time-out period.
 

j4n

Member
imo there is no 'better' or 'worse' command. It all depends on what you want to do.
Advantages of FOR FIRST are:

  • transaction scope
  • faster -> especially because you don't have to use the "NO-ERROR" phrase
I might be wrong but I also thought that FOR statements can use multiple indexes and FIND statements can't. Anyway - it all depends on what you want to do. You can't say something is better in general.
 

TomBascom

Curmudgeon
That looks like a unique record.

In which case you shouldn't be using FIRST at all.

Using FIRST in that situation is pointless and has no performance impact at all. Zero, zip, nada and nil.

From a code maintenance and quality perceptive it is bad practice. You are implying that there is a possibility that more than one record might meet your WHERE criteria. Yet you are making the FIRST of the implied set magic by assigning it a value that no other record in the implied set will have. If nothing else this is a relational faux pas of there really is a set of records rather than just one. In my experience it is also the root of some rather nasty data driven bugginess.

FIRST is a big red flag. Especially when it is being used in a knee-jerk manner in conjunction with every FIND statement.
 
Rule of thumb and good practice.
U should only really use FOR / FOR EACH with a query.
Also do U really require a FIND statement, couldn`t a CAN FIND be used here.
Does the record have to be in the buffer?
 
Disagree.
For first/each exclusive-lock is worst than find first exclusive-lock.
Bacause it random locks a lot of records, even records that is not in records set. It causes big problems and random errors that hard to identify in multiuser enviroment.

Find first itself is not bug.

Goog practice is to use
define buffer table2 for table.
for each/first table where <condition> NO-LOCK:

find (first) table2 where ROWID(table2) = rowid(table1) exclusive-lock no-wait no-error.
if available table2 then
do:
<make changes>
end.
end.
this way you manage how many records will be locked.

As for performance, it doesnt matter in your case. If 1 one field in where condition. If condition is more complex then it is better to use for each/first. It can use many indexes in 1 query. find (first) always use only 1 index.

Maxim.
 

RealHeavyDude

Well-Known Member
Usually I consider FIND FIRST as bad practice. FIND is supposed to exactly fetch one record that is unique. By design it raises an error when there is no record or there are more than one record that satisfies the selection criteria. That's exactly what you want most times. Especially when the uniqueness is not enforced with an unique index it will protect you against uniqueness violations going unnoticed. When there are more than one record which satisfies the selection criteria FIND FIRST will always fetch a record and you'll never know whether this was the only one. Coding this way you introduce the potential of silently corrupting your data in discarding your business rules and you will never trap an error telling you.

IMHO of course, RealHeavyDude.
 
Top