Question FIND LAST/FIRST

[Rajat Sharma]

Hello everyone, hope you all are well!!

I have a little confusion with FIND LAST/FIRST statement. I think, indexes are stored in ascending order if we define them as “A” otherwise in descending order with “D” (please suggest). So basically FIND LAST and FIRST will search (index or table search) from bottom to top or top to bottom respectively (no matter how indexes are stored). So in equality matching, if we are doing find last/first then does that make any difference (I think it shouldn't) because it will search all the records(please suggest).

For example:

 FIND LAST c-data
   WHERE c-num  = l-num
   AND  c-val   = l-value
   AND  c-seq  = l-seq.

In above code: c-num, c-val, c-seq are part of one index (KEY) and defined in the same order. All the index fields are of type “A”. So is there any specific reason that when should we use LAST or FIRST.

Apart from this, I read about idxbuild, idxfix and idxcheck but I didn't understand that how could I use these utilities to increase performance. Are these utilities should only use by DBA (please suggest).

Thanks & Regards!
Rajat.

 

[TomBascom]

FIRST (or LAST) implies a set of multiple results. While it is possible to process a set of records FIND is not really well suited to result sets. That is what FOR EACH is for. Or queries (dynamic or static with DEFINE QUERY etc).

If all of your criteria are equality matches and and all components of the index are in use and the index is defined with the uniqueness constraint or you know that only one record can possibly meet those criteria (perhaps another index makes it so) then you have a UNIQUE FIND.

If you are searching for a UNIQUE record then using FIRST and LAST makes no sense. You should not do it. Even though there is a lot of legacy code that does so. It is a "worst practice" which needs to be extinguished.

If someone tries to tell you "it is faster" then they are mistaken. It is only faster if it is also wrong. Do your users want the wrong answer faster?

For a unique FIND there is zero advantage to adding FIRST or LAST. Feel free to attempt to prove that I am wrong.

None the less in some very badly managed development shops it is the standard to *always* use FIRST on all FIND statements. This is one of the most foul development standards in the Progress world. It is much worse than CODING IN ALL UPPER CASE. Even if you have had the dreadful misfortune to work with a code base inflicted with such drivel you have to power to just stop doing it.

Using FIRST or LAST also implies that the programmer has a specific order in mind. If there is an order then you are in trouble. Ordering in FIND statements is a result of index selection -- how does the programmer know what index will be selected? With the FIND statement you could force it with use USE-INDEX but that is another "worst practice" with plenty of its own drawbacks.

If a set of records is being returned what makes the FIRST record special? If it is special why is there no attribute (a field) which represents that specialness? If there is such a field why isn't it being used in the WHERE clause? (Hint: you are violating 3rd normal form if the first record in a result set is somehow magically special...)

The right way to obtain a set of records in a particular order is to use "FOR EACH table BY ..."

 

[TomBascom]

Your understanding of the behavior of ascending and descending is correct.

The index fix and index rebuild utilities are DBA utilities. They can sometimes improve performance a little bit by reorganizing the indexes in ways that will result in more efficient use of storage and thus less IO. Most of the time running them has very little visible performance impact to end users.

They can also used to repair the database in the event that corruption occurs.

 

[TomBascom]

An example of one of the few sort of acceptable ways to use FIND FIRST:

find first customer no-lock no-error.

do while available customer:

  /* do something with customer record */

  find next customer no-lock no-error.

end.

Notice how easy it would be to replace this with FOR EACH customer?