Forum Post: RE: For First, For Last, For Each

[rugadillo]

This is an interesting topic...I am a new programmer and very new to the performance nuances of writing Progress queries. I shared this thread with a fellow programmer who is very experienced with it. Here is his take: *** I guess the way I’ve always interpreted the BY functionality, it seems to me the problem is an obvious issue with how they are using the BY clause on the FOR FIRST/LAST. The BY sorts the records after they have all been retrieved in the order dictated by the index. Thus the FIRST/LAST is only going to get the one record based on the index, there is no sorting that is ever going to occur. From the OE help: BY expression [ DESCENDING ] Sorts the selected records by the value of expression This is also something to consider in (browse) queries as it can be a huge performance issue. The query must first find all the records that meet the query specifications before it can do the BY sorting. 2 examples to illustrate FOR EACH speed issue. Take a table with 30,000 records 1) Say you want to get all the records for one person and sort by date. You use an index that has the person’s name-in. That query finds 100 records, they are sorted by date and returned. Now if you have a browse displaying only 20 at a time, the same look up of finding 100 records each time needs to be done each page change. Which really isn’t all that bad considering, if you really need a browse to display like that and no proper index is currently available (a better index should be added for next schema cut though and then the program updated). 2) For the second example you want to show every record sorted by date again, but for all the people, and there is no index with the date to narrow down the query. Now the query has to read all 30,000 records first before it can sort on the date. So if you have the browse showing 20 records, all 30k records are read. Then to query the page to the next 20 all 30k records need to be read again. So that would probably result in an unworkable/unbearable program.

Continue reading...