Find / for first...

[rzr]

A while ago, I read a thread on FIND / FOR FIRST and it said that we should avoid using FIND FIRST / FOR FIRST stmts - it was not good practice. I'm not sure if I understood or still understand why is that so
I was "told" / "taught" to use them and I never really questioned why ! Ton's of prog's in my app use them too....

So if someone could clearly explain why is it a "not so good" practice to use FIND FIRST or FOR FIRST - it will be much appreciated !!

 

[MaximMonin]

1. Many small queries slow down system.
But if it is tty session or appserver thread which resides on db server it works fast anayway.
In client-server mode many find first slow down system a lot.

2. If you add to your tables new fields it is possible that there will be many records that exacts your query with find first. So you have to change it to for each anyway.
3. Find first uses only 1 index (for first/for each can use many indexes) So you have to create index with all fields in find query.
in case of for each you can create many indexes with 1 field per index.

 

[rzr]

Thanks MaximMonin !!

I understand #2 & #3...
Could you please elaborate #1?? "In client-server mode many find first slow down system a lot.” - How / why does it slow down the system? thanx....

 

[MaximMonin]

Each find first is a query to DB server. Db server finds a record and pass it through network to client.
If it is cycle with 1000 queries it results to 1000 transfers through network
for each table1:
find first table2 of table1.
end.

for each table1, each table2 of table1:
end.

is 1 query returns a lot records to clients once. Db server selects all records once.

My estimation - it can slow down system in 10+ times.

 

[rzr]

good to know!! thanks for the info...

 

[Elod]

Also not to forget that in an execution block (let's say a procedure) by using the FIND FIRST statement, the record buffer is still available after the statement,
While using FOR FIRST/EACH block statement the record goes out of scope when the execution of that block has ended.

 

[RealHeavyDude]

That would be buffer scope :awink: ( free reference in this case ) ...

Heavy Regards, RealHeavyDude.

 

[tamhas]

This must be one of the more persistent examples of where there is a frequent shop standard which is actually bad practice. Why is it bad practice? Well, if you do a find first, one of two things is true.

1. The criteria specified is a unique find. If so, the first is deceptive since it conveys to anyone reading the code later that more than one record could match the criteria, so it is confusing.

2. The criteria specified do not specify a unique find. If so, this implies that you are treating the first record in a group differently than the rest of the records in the group, which is a violation of normalization.

Some people have the idea that find first is somehow faster than find for unique criteria because the server does not need to check to see whether 1 record or more than one match the criteria. BS. I defy anyone to provide a test demonstrating this.

 

[medu]

Some people have the idea that find first is somehow faster than find for unique criteria because the server does not need to check to see whether 1 record or more than one match the criteria. BS. I defy anyone to provide a test demonstrating this.

i think it's not only the believe, it simply an extra step in the find algorithm... if first is not used it look to see if there are any other records, now of course it's not stupid enough to keep on parsing all records and it stops when it finds the second one that matches the criteria and raise the 'ambiguous' error. In most cases, when an index is usable given the criteria there won't be any significant difference... however, examples of badly written queries (or table structure) can be found

DEF VAR i AS INTEGER NO-UNDO.
DEF VAR j AS INTEGER NO-UNDO.
DEF VAR t AS INTEGER NO-UNDO EXTENT 2.

FOR EACH order-line:
END.

ETIME(TRUE).
DO j = 1 TO 1000:
    DO i = 0 TO 5:
        FIND FIRST order-line WHERE backorder EQ TRUE AND discount GT i * 10 NO-ERROR.
    END.
END.

t[1] = ETIME.

ETIME(TRUE).
DO j = 1 TO 1000:
    DO i = 0 TO 5:
        FIND order-line WHERE backorder EQ TRUE AND discount GT i * 10 NO-ERROR.
    END.
END.
t[2] = ETIME.

MESSAGE 'first: ' t[1] SKIP 'unique: ' t[2]
    VIEW-AS ALERT-BOX INFO BUTTONS OK.

 

[tamhas]

Of course, you didn't post any results ...

But, consider this test in relationship to my remarks.
1. The find is intentionally non-unique and, moreover, intentionally does not use indices.
2. The find is done only for the purposes of testing. You don't actually DO anything with the first record that is different than the others.
3. You find the same record over and over again thousands of times in order to detect whatever difference you detected.

I don't see any of that as justifying bad practice in real programming.

 

[medu]

true, it's merely to answer your defy so it's intentionally doing all those things

that's on (in)famous sport database, results on my machine: 20 vs 29 seconds...

as a side note I don't even remember when I've last used find for data retrieval (if ever), for each is far more simple to write that find no-error/if available... find current exclusive it's one of the exceptions.

 

[TomBascom]

define variable z as integer no-undo initial 1000000.

find first customer no-lock.

etime( yes ).
do i = 1 to z:
end.
display etime.

etime( yes ).
do i = 1 to z:
  find first customer no-lock.
end.
display etime.

etime( yes ).
do i = 1 to z:
  find customer where custNum = 1 no-lock.
end.
display etime.

etime( yes ).
do i = 1 to z:
  find first customer where custNum = 1 no-lock.
end.
display etime.

Results: 401 15,113 16,326 16,337

The last two test cases are the crux of the argument and, as you can see, the difference is well within "noise" and, in this case FIRST lost.

 

[rzr]

muito obrigado !!! I'm going to erase the FIRST (w/ FIND) from my dictionary ..