Conditions in FOR EACH which is TRUE regardless of database records?

Question

FOR EACH loan WHERE /* Condition1 */ loan.date = SomeDate
                AND /* Condition2 */ loan.type = SomeType
                AND /* Condition3 */ (IF SpecMode THEN TRUE ELSE loan.spec = SomeSpec)

Condition3 = TRUE when SpecMode = TRUE. So I wonder if code above and code below would work exact the same (incl. speed) when SpecMode = TRUE?

FOR EACH loan WHERE Condition1 
                AND Condition2
                AND TRUE

Or even like this?

FOR EACH loan WHERE Condition1 
                AND Condition2

Generally speaking question is how Progress manages conditions which can be evaluated regardless of database records. Does it take more time? Also links about how Progress works in more deep view would be appreciated.

Add 08/16/13: Code I'm working with initially was:

IF SpecMode THEN
  FOR EACH loan WHERE Condition1 AND Condition2:
    RUN Proc.
ELSE
  FOR EACH loan WHERE Condition1 AND Condition2 AND Condition3:
    RUN Proc.

Dynamic queries was first idea came to my mind, but I realized that it mean rewriting of all nested code with dynamic queries style (which is imo has worse readabilty, I moved this to separate question). I want to go with modern approach so I will do it if it's most reasonable solution.

There is third way which keeps code in "static style". It is using include files with parameters. But it means another .i file in already huge codebase. And I generally hate this approach (as well as "cheating" with preprocessor constants containing code). Our system is big and old and full of this kind of things, which is harder to analyze and basically seems to be just not right.

Michael Lonski from Allegro has an excellent presentation on how Progress uses indexes called "Pick An Index, Any Index...", access it here http://www.allegroconsultants.com/downloads?func=select&id=3 — TerryB, Aug 15 '13 at 17:14
@Terry Bochaton PDF was like table of contents. I think whole presentation could clear a lot for me. Any more read in this area? Thanks! — Progressive, Aug 16 '13 at 09:01

Tom Bascom · Accepted Answer · 2013-08-15T19:46:16.090

The ( IF ... THEN ... ELSE ... ) embedded in the WHERE clause is treated as a function.

In general it is preferable to avoid having function calls in a WHERE clause. Some, but not all, can be resolved on the server side. (User-defined functions and CAN-DO() are two examples that must be evaluated on the client.)

In your case I don't know that the IF is always true. If specMode is true then it is but if it is false the ELSE portion is evaluated and I can't know how that works out without looking at the data. So it would not necessarily be correct to replace it as you suggest.

If specMode is always true then, yes, replacing it as you suggest (both versions) will work.

From an efficiency point of view the use of the IF function eliminates the ability to bracket on the loan.spec field. If loan.date and loan.type are leading components of an index then they will be used to bracket. If loan.spec is also an index component it cannot, however, be used to improve the selection and the whole subset of records carved out by the other criteria will need to be examined individually.

I think that the code you are showing is probably being used to provide a "clever" option of showing either ALL records with date and type, or just a subset of date, type and spec. This might have been an attractive (but hard to read and inefficient) approach back in the old days. It would have allowed a single block of code to handle whatever is inside the loop body.

In today's world you don't need to do that. You would just write a dynamic query and populate a proper WHERE clause as a string that you then use QUERY-PREPARE on. That way if loan.spec is part of an index it can be used to help support the query and those cases will run much faster.

@ Tom Bascom I've got your point, Tom. Look at addition in question. — Progressive, Aug 16 '13 at 07:52
1) avoid include files and pre-processors -- there's no need to make a bad situation worse. 2) you can mix and match static and dynamic constructs as appropriate -- you don't need to go the fully dynamic everything route just to get flexible query criteria. — Tom Bascom, Aug 16 '13 at 13:46

score 2 · Answer 2 · answered Aug 16 '13 at 07:43

As an addon to Toms excellent answer, a tip is to compile with xref whenever you are unsure of what indices are used.

COMPILE file.p [SAVE] XREF file.txt.

That will genereate a file containing index usage (amongst other things). Look for rows like this:

c:\temp\program.p c:\temp\include.i 21 SEARCH db.webkonv tkn WHOLE-INDEX

SEARCH indicates that index "tkn" of the table "webkonv" is used in this example. WHOLE-INDEX indicating that the entire index has been scanned (in this case that was expected - this code was used before the TABLE-SCAN directive). Check the ABL Reference on the COMPILE statement.

score 1 · Answer 3 · answered Aug 30 '13 at 01:46

for your case:

EACH loan WHERE /* Condition1 */ loan.date = SomeDate
        AND /* Condition2 */ loan.type = SomeType
        AND /* Condition3 */ (IF SpecMode THEN TRUE ELSE loan.spec = SomeSpec)

You can try like this:

your_loop:
EACH loan WHERE /* Condition1 */ loan.date = SomeDate
    AND         /* Condition2 */ loan.type = SomeType
    : 
    /* Condition3 */ 
    IF not SpecMode THEN DO:
        If loan.spec <> SomeSpec THEN
            NEXT your_loop. /* both false, next loop */
    END.

    /* ........ do your work ...... */

END.

I suppose it will be the same in the terms of time? – Progressive Sep 02 '13 at 05:57 — Progressive, Sep 02 '13 at 05:57

score 1 · Answer 4 · answered Aug 30 '13 at 14:58

I can't see any sense in use a for each that does not mention any field of the table..... the whole table will be selected regardless if the condition is true or false, since there is no inquiry to the table.

In any case Progress evaluates the full condition and retrieve the set of record that matches that condition.

Example 1.

def var as logical init false.

for each items where a:

display item.code. /* Will display nothing but read all records of the table */

end.

Example 2:

def var a as logical init true.

for each items where a:

  display item.code.    /* Will display all records of the table */

end.

Example 3:

Define variable v_user_can_read_items init false.

For each item where item.code begins "4" and

                v_user_can_read_items = true:


 display item.code.  /* will display nothing but read all items starting by 4 */

End.

The time taken will depend on the where condition, on the first 2 cases will be slow, because is reading ALL the records. In teh third case will be fast, because is first selecting the set of items starting by 4 and then don't displying because the condition return false.

The shortest tilme is when is using an appripiate index (where condition does) and the retrieved set is a short set of data.

Note that 1st case is faster than 2nd (because founded records are not transfered). — Progressive, Sep 02 '13 at 05:59

score -1 · Answer 5 · answered Apr 24 '14 at 12:36

-1

Define variable v_user_can_read_items init false.

For each item where item.code begins "4" and

            v_user_can_read_items = true:

display item.code. /* will display nothing but read all items starting by 4 */

End.

answered Apr 24 '14 at 12:36

user3568840

1

Conditions in FOR EACH which is TRUE regardless of database records?

5 Answers5