FIND FIRST vs FOR FIRST (moved)

[tamhas]

Starting a new thread because the indention has gotten silly and the topic has changed several times.

Enoon said:

Is FOR FIRST better then FIND FIRST, why?
What about replacing FIND and FIND FIRSTs (there are no uniqunesses used) with
Code:
FOR FIRST <record>: END.
since the compiler allows it and the record is also available after the FOR FIRST?

Any thought about this?
--------------------------------------
FIND FIRST is deceptive since there is no way to specify a sort order. Consequently, it either needs to be a unique find, in which case the FIRST is superfluous and misleading or it it is going to return an arbitrary record out of the set with matching keys, which is silly.

FOR FIRST solves this by allowing you to specify a sort order and thus make it clear that the same record will be selected every time. However, this is also suspicious since it violates normalization rules to treat the first record in a set differently than the rest.

CAN-FIND( FIRST makes some sense since there are times when one wants to know whether or not the set is empty.

 

[parul]

FOR can use multiple indexes FIND uses only single index.

To add to question is:
FIND FIRST better than FIND ...performance wise?

 

[TomBascom]

Multi-index selection is over-rated. It is hardly ever beneficial.

You should also probably be thinking in terms of converting this sort of syntax to a QUERY rather than a FIND or a FOR anyway.

 

[lord_icon]

WELL said Tom.
Using FIND & FOR are old methods and not very efficient use
of the language.
It is rather 3GL and not keeping with the
times.
OpenEdge is ABL - THE future for business language
logic.

 

[enoon]

Multi-index selection is over-rated. It is hardly ever beneficial.

You should also probably be thinking in terms of converting this sort of syntax to a QUERY rather than a FIND or a FOR anyway.

Why would that be over-rated? Because of performance loss upon cross-referencing multiple indexes?

Also is it any faster usage of a QUERY instead of FIND or FOR EACH in terms of performance, is there an explanation?

And a little bit out of topic, dynamic QUERY vs. Static QUERY. In terms of performance is it any faster?

PS: Thanks for moving this topic.

 

[TomBascom]

Why would that be over-rated? Because of performance loss upon cross-referencing multiple indexes?

It is a feature that sounds great. But in practice it hardly ever improves performance.

Also is it any faster usage of a QUERY instead of FIND or FOR EACH in terms of performance, is there an explanation?

No, it isn't faster. But it isn't slower either. It is a cleaner coding structure for many purposes.

And a little bit out of topic, dynamic QUERY vs. Static QUERY. In terms of performance is it any faster?

It shouldn't matter.

Having said that... on older releases there is an issue with large result sets creating potentially needless temp-files. That additional IO can slow things down. Use the forward-only attribute or -noautoresultlist to prevent the creation of the sort file.

 

[TomBascom]

If using FIRST is making your code faster it is NOT a good thing.

Do not use FIND FIRST "because it is always faster". This is a fallacy. (Much like "kill -9 always works".)

The only time that FIRST will improve performance is when a query that you think should be returning a single record in fact returns a great many records.

If you use FIRST to improve the performance of such a query you are playing with fire. By doing so you are adding an implied attribute ("firstness") to that record. But this attribute is not actually part of the record and many things can cause "firstness" to shift to a different record.

This is especially pernicious if you are updating the FIRST record and expecting future queries to return that now "magic" record. It is one of those programming practices that sometimes fails catastrophically leading to bugs which can be very difficult to pin down and even more difficult to fix (imagine telling your boss that you have to wade through 10,000 programs and change hundreds of thousands of lines of code because of a bad habit).

There are a few times when it is more or less ok to use FIRST. The ones that I can think of at the moment:

1) You are starting the process of looping through records in a situation where, for some reason, FOR EACH isn't appropriate. Something like this:

find first customer no-lock no-error.
do while available( customer ):
  /* something */
  find next customer no-lock no-error.
end.

2) You would be just as happy with FIND ANY or FIND RANDOM (if that syntax existed).

If FIRST is making a query faster then you need to either improve the WHERE clause or you need to add an appropriate index to support that query.

 

[Kladkul]

WELL said Tom.
Using FIND & FOR are old methods and not very efficient use
of the language.
It is rather 3GL and not keeping with the
times.
OpenEdge is ABL - THE future for business language
logic.

So then, FIND FIRSTs were used more in older versions of PROGRESS? IE: Still used for backward compatability?

 

[TomBascom]

FIND is an older statement. It does still have its uses but newer features can be better for a lot of things.

Using FIRST automatically whenever you type FIND has never been a good practice.

 

[shireeshn]

Hi TamHas,
This problem i have faced in my project, wht TomBascom has suggested was done by me tooo.

This is good qusteion i feel to boz we have used the word First means first record.

But can we come to a conclution that "Find First" is the first record created(recid) and for each is the index record will be show ?

 

[enoon]

Hi TamHas,
This problem i have faced in my project, wht TomBascom has suggested was done by me tooo.

This is good qusteion i feel to boz we have used the word First means first record.

But can we come to a conclution that "Find First" is the first record created(recid) and for each is the index record will be show ?

FIND FIRST will retreive the first record that matches the where condition using one index. If none indexed field is used, then will be the first RECID from the index. FOR FIRST, in case of indexed fields used in WHERE condition, can use multiple indexes. But in the case FIND FIRST and FOR FIRST have no WHERE condition both will retreive the first record created with the smallest RECID/ROWID.
The difference can be only when they have WHERE condition, it can retreive different records, because of the different sorting. I'm not 100% sure about this last statement.

 

[TomBascom]

No.

There is no guarantee regarding which record will be returned by FIND FIRST. The ordering of records within the result set is undefined. It is NOT by creation date & time. Nor can you assume that today's first recid will be tomorrows first recid. You get whatever happened to be the first record found by the query under whatever circumstances prevailed. Many things can, and will, change the ordering.

There may very well appear to be a "safe" ordering in circumstances that you may test. Do not believe it. Using the FIRST record for special purposes leads to very subtle and nearly impossible to pin down bugs. When you always code FIRST it is very easy to accidentally fall into this trap and never even know it. Pretty soon you're just shrugging off "weird" and mysterious production behaviors as if they are part of the normal background.

 

[enoon]

No.

There is no guarantee regarding which record will be returned by FIND FIRST. The ordering of records within the result set is undefined. It is NOT by creation date & time. Nor can you assume that today's first recid will be tomorrows first recid. You get whatever happened to be the first record found by the query under whatever circumstances prevailed. Many things can, and will, change the ordering.

There may very well appear to be a "safe" ordering in circumstances that you may test. Do not believe it. Using the FIRST record for special purposes leads to very subtle and nearly impossible to pin down bugs. When you always code FIRST it is very easy to accidentally fall into this trap and never even know it. Pretty soon you're just shrugging off "weird" and mysterious production behaviors as if they are part of the normal background.

Could you give us a concrete example for us to test this? I've seen this reply but did not found any working example in the message to prove this.

 

[tamhas]

For starters, doing a dump and load means that every record gets a new RECID and not necessarily in any particular order. Presto, your find is finding a different record. RECIDs are not assigned sequentially anyway. If you need ordering by creation order, put in a field for it, index on it, and specify it in the WHERE clause. Any tricky assumptions you make not only obscure the code for the next person, but open yourself to being shot in the foot. Be explicit. Be safe.

 

[enoon]

For starters, doing a dump and load means that every record gets a new RECID and not necessarily in any particular order. Presto, your find is finding a different record. RECIDs are not assigned sequentially anyway. If you need ordering by creation order, put in a field for it, index on it, and specify it in the WHERE clause. Any tricky assumptions you make not only obscure the code for the next person, but open yourself to being shot in the foot. Be explicit. Be safe.

Yes, thank you, I knew the dump/load thing, but didn't quite make the connection with the FIND FIRST. And yes I had in the past tricky bugs to pin down, from this reason.