Answered Query performance

Hello everyone,

It's been a while. I hope you are all doing well.

While making some new app, I was asking my self this:
I have a table of purchase order that as the type of purchase ordre in index.
I want to display 3 differrent purchase order.

So I was thinking of two solution:
1.
Code:
DEF VAR listType AS CHAR NO-UNDO.
DEF VAR k AS INT NO-UNDO.

listType = "type1,type2,type3" .

DO k = 1 TO NUM-ENTRIES(listType):
    FOR EACH ORDER WHERE ORDER.ordtyp = ENTRY(k,listType)
                         ORDER.ordcomplete = FALSE NO-LOCK:

    END.
END.

2.
Code:
DEF VAR listType AS CHAR NO-UNDO.

listType = "type1,type2,type3" .

FOR EACH ORDER WHERE LOOKUP(ORDER.ordtyp,listType) > 0
               AND     ORDER.ordcomplete = FALSE NO-LOCK:

END.

What do you think and why ?
 

Cringer

ProgressTalk.com Moderator
Staff member
Both options will almost certainly result in a whole index read of the Order table. Functions in query predicates are typically hard to resolve and so you end up with poor performance.

You could use an OPEN QUERY or a Dynamic Query to solve the problem more efficiently.
 

andre42

Member
If there is an index on ORDER.ordtyp (or preferably on ORDER.ordcomplete and ORDER.ordtyp) then option 1 should be faster. Usually I introduce a variable to put the return-value of ENTRY, but I don't know how much of a difference that makes, ie. if ENTRY is evaluated more than one time per iteration of k.
Code:
DEF VAR listType AS CHAR NO-UNDO.
DEF VAR lt AS CHAR NO-UNDO.
DEF VAR k AS INT NO-UNDO.

listType = "type1,type2,type3" .

DO k = 1 TO NUM-ENTRIES(listType):
    lt = ENTRY(k,listType).
    FOR EACH ORDER WHERE ORDER.ordtyp = lt
                         ORDER.ordcomplete = FALSE NO-LOCK:

    END.
END.
Regarding what Cringer writes about functions in query predicates: I think this mostly bad if you apply functions to the record you are searching (like in option 2) because than no index can be used.
 

Cringer

ProgressTalk.com Moderator
Staff member
I agree the first will be better, and assigning the ENTRY() result to a variable will make a difference.
 

Cringer

ProgressTalk.com Moderator
Staff member
I was a bit busy earlier, but I have had time to write a more efficient solution for the Sports DB as an example:

Code:
DEFINE VARIABLE cCountries      AS CHARACTER NO-UNDO INITIAL "USA,Finland,France".
DEFINE VARIABLE i               AS INTEGER   NO-UNDO.
DEFINE VARIABLE cCurrentCountry AS CHARACTER NO-UNDO.

DO i = 1 TO NUM-ENTRIES (cCountries):
    cCurrentCountry = ENTRY (i, cCountries). 
    
    OPEN QUERY CountryQuery FOR EACH Customer NO-LOCK WHERE Customer.Country = cCurrentCountry. 
    
    DO WHILE QUERY CountryQuery:GET-NEXT ():
        DISPLAY Customer WITH 1 COL SCROLLABLE. 
    END. 
    
    QUERY CountryQuery:QUERY-CLOSE ().
END.
 
@Cringer what is the difference between what you wrote and what @andre42 wrote ?

I usually go with a for each loop than creating a query and navigate through it ? What is it changing ?

Thank you in advance :)
 
Last edited:

TomBascom

Curmudgeon
@Cringer what is the difference between what you wrote and what @Osborne wrote ?

I usually go with a for each loop than creating a query and navigate through it ? What is it changing ?

Thank you in advance :)

Osborne hasn't replied in this thread. Are you asking about the difference between Cringer and Andre42's code?

They are both good ways to run the query using a direct equality match to a variable rather than embedding a function call in a WHERE clause.

Personally, I like Andre's use of a static query better, that's usually easier to understand if there is no compelling reason to have a dynamic query.

Both posters lose efficiency points for embedding a call to NUM-ENTRIES() in their DO loops.

Andre loses style points for abbreviating keywords "DEF VAR", "CHAR", "INT", etc.

I find Cringer's indentation a bit nicer to look at but I really dislike the use of gibberish coding of perfectly good variable names.

Both posters lose points for UPPER CASE KEYWORDS.
 
Yeah true I was aming for Andre42. Missing some sleep on this one.

So by convention keywords should be in lower case ?

Yeah my question was mainly about the querys .

@TomBascom what would you do instead of embedding a call of NUM-ENTRIES() in a DO loop for this cases ?
 

andre42

Member
Personally, I like Andre's use of a static query better, that's usually easier to understand if there is no compelling reason to have a dynamic query.

I thought what Cringer wrote is a static query? As far as I understood a dynamic query is when you use a query object, build a query string and use the query-open method on the query object.
Usually (ie. if there is no compelling reason to use a static or dynamic query) I use simple for each statements like in this code.

Both posters lose efficiency points for embedding a call to NUM-ENTRIES() in their DO loops.

Andre loses style points for abbreviating keywords "DEF VAR", "CHAR", "INT", etc.

I find Cringer's indentation a bit nicer to look at but I really dislike the use of gibberish coding of perfectly good variable names.

Both posters lose points for UPPER CASE KEYWORDS.

I just copied Boby´s code and changed the minimum to illustrate my point. In my own code I don't abbreviate keywords (the only ones where I might do this are min(imum), max(imum) and init(ial) since these look too verbose on occasion) and I don't use upper case keywords (except in AppBuilder generated code I can't control directly).
I didn't bother to fix variable names and indentation for this simple example.
Good catch about the function call in the do loop. I keep forgetting that Progress is incapable of the most simple optimizations and will call the function for each iteration :D. Usually I avoid an additional variable in this instance to make the code more readable.
 

TomBascom

Curmudgeon
I thought what Cringer wrote is a static query? As far as I understood a dynamic query is when you use a query object, build a query string and use the query-open method on the query object.

Good point.

Being an old dinosaur I tend to lump together all of the new-fangled query stuff like OPEN QUERY with the handle based stuff and call them both "dynamic queries". But that isn't really correct. Thanks for catching that.
 

TomBascom

Curmudgeon
So by convention keywords should be in lower case?

That is certainly my preference.

Lower case is much easier on the eyes. It also helps to spread out bit-wear in memory chips ;)

Plus I really don't want people thinking that my code is anything like the code in the documentation or that which is created by the app builder etc.

what would you do instead of embedding a call of NUM-ENTRIES() in a DO loop for this cases?

Code:
define variable n as integer no-undo.

n = num-entries( listOfStuff ).

do i = 1 to n:
  . . .
end.
 
Last edited:

Cringer

ProgressTalk.com Moderator
Staff member
In reality assigning the NUM-ENTRIES () to a variable probably makes very little difference, but Tom is correct, that one probably should.
I'll leave the religious wars on keyword casing to others. As well as the variable naming conventions. I write my code based upon the conventions of the company I work for... ;)

@Boby, you are right, in this instance there is little difference between my code and the other example given. I was showing how you could use OPEN QUERY to get around a more complex problem.
 

TomBascom

Curmudgeon
For relatively short delimited lists there is virtually no impact to embedding num-entries() in the loop.

For very long lists it does become noticeable.

It can be hard to predict how long a list is going to be at runtime. Those pesky users have a bad habit of using software in ways that the coder never anticipated :(

If you form the habit of using the variable you won't have to worry about how long your lists are.
 
@Boby, you are right, in this instance there is little difference between my code and the other example given. I was showing how you could use OPEN QUERY to get around a more complex problem.
I see and thank you for the point made it gives me more idea


That is certainly my preference.

Lower case is much easier on the eyes. It also helps to spread out bit-wear in memory chips ;)

Plus I really don't want people thinking that my code is anything like the code in the documentation or that which is created by the app builder etc.



Code:
define variable n as integer no-undo.

n - num-entries( listOfStuff ).

do i = 1 to n:
  . . .
end.
Thank you @TomBascom for the detailled answer as always.

Also I understand why taking this automatism is important.

Thank you all :)
 

KrisM

Member
That is certainly my preference.

Lower case is much easier on the eyes. It also helps to spread out bit-wear in memory chips ;)

Plus I really don't want people thinking that my code is anything like the code in the documentation or that which is created by the app builder etc.



Code:
define variable n as integer no-undo.

n = num-entries( listOfStuff ).

do i = 1 to n:
  . . .
end.

My suggestion that does not need an extra variable:

Code:
do i = num-entries(listOfStuff) to 1 by -1:
   ...
end.
 

Stefan

Well-Known Member
The point of the extra variable is to avoid evaluating num-entries() with each iteration of the loop.
And Kris' point is that by swapping the order of the loop, you do not need an extra variable. The num-entries function in the from part of the loop is evaluated once when the loop starts, the to is executed as often as there are iterations.

Although making loop orders potentially convulted to save a variable is not my style.
 

Cringer

ProgressTalk.com Moderator
Staff member
It does indeed work. Although I agree with @Stefan that the complication is bad..

Code:
FUNCTION MyFunc RETURNS INTEGER  ():
    MESSAGE 111
        VIEW-AS ALERT-BOX.
    RETURN 10.
END FUNCTION.

DEFINE VARIABLE i AS INTEGER NO-UNDO.
DO i = myfunc () TO 1 BY -1:
END.
 
Top