Answered When Is Appropriate To Use Of Nested For Statements

progdev1

Member
Hi,
I have a quick question related to nested for statements.

By nested FOR statements I mean
Code:
FOR EACH customer NO-LOCK,
     EACH sales-order WHERE sales-order.customer-id = customer.customer-id EXCLUSIVE-LOCK NO-ERROR:
    ASSIGN sales-order.status = "archive".
END.

When I started learning PROGRESS first, I was taught that nested FOR statements were generally faster, but had the disadvantage of being less controllable. E.g. in the above code example you could have problems with locking, record scoping and depending on indexes you could have situation whereby you might have to retrieve all customers before you could update any sales-order. I was told to avoid using them when doing a search (no-lock) and never use them when doing an update.

For that reason, unless I'm writing a query for a browse I tend to avoid them completely and write something like.
Code:
DEFINE BUFFER b-sales-order FOR sales-order.

FOR EACH customer NO-LOCK:
     DO TRANSACTION:
        FOR EACH sales-order WHERE sales-order.customer-id = customer.customer-id NO-LOCK:
            FIND FIRST b-sales-order where b-sales-order.order-no = sales-order.order-no 
                       EXCLUSIVE-LOCK NO-ERROR NO-WAIT.  
            IF AVAILABLE b-sales-order THEN DO:
                ASSIGN b-sales-order.status = "archive".
                RELEASE b-sales-order.
            END.
        END. /* FOR EACH*/
    END. /* DO TRANSACTION */
END.

The company I'm doing work for at the moment tend to use nested FOR statements. Does anyone know if my understanding of the advantages/disadvantages of nested for statements is correct. Also if anyone know maybe you could confirm when is it approriate to use and not use nested if statements.
 

RealHeavyDude

Well-Known Member
Nested for each statements are not bad in general. They are perfectly okay when reading or updating the parent record.

But, they are bad in the example you post: Updating records of child buffers because of the reasons you describe.

It is good practice to use defined buffers and strong scoping ( buffers and transactions ) when updating the database to avoid potential locking issues and transactions scopes larger than need be. Furthermore, it makes your code much better understandable - otherwise you always need to have the default behavior in mind when trying to understand what the code does versus it probably should do.

Therefore I like your code for using a defined buffer and explicitely scoping the transaction. Still it contains ONE BIG RED FLAG: The release statement. For one the release statement certainly does not do what most people think it does and, secondly, it is almost always used in an ill-advised attempt to circumvent buffer scoping issues. In your case you should remove the release statement and use strong scoping ( do for b-sales-order: ) instead.

Heavy Regard, RealHeavyDude.
 

progdev1

Member
Thanks a million for that. As regards release statement. You are correct, I tend to put them in out of habit. I read about this recently and it should also be noted that a release statement doesn't release the record straight away. What it does is tell progress you want to release the record, but Progress decides when the record is actually released; which is at the end of the transaction scope.
 
Top