Performance of for each

Posted by Venky on 16-Apr-2015 00:20

I have a question, tried for a practical solution but didn't help.

Which of a) and b) is good for performance and why. Please also explain negative impact of the other query

a) for each customer where country = "USA" and state = "NY" and city = "new york" no-lock:




   for each customer where country = "USA" and state = "NY" and city = "buffalo" no-lock:




b) for each customer where country = "USA" and state = "NY" and (city = "new york" or city = "buffalo") no-lock:





All Replies

Posted by James Palmer on 16-Apr-2015 02:30

A lot of this completely depends on the indexes you have defined.

1) Compile your queries with XREF and see what indexes they are using. Check also for whole index reads.

2) Run each of the queries with ProTop table stats and see how many records are read and how many are returned.

3) Consider another option:

Load your options for city into a temp table and add it as a join on your query.

Posted by Venky on 16-Apr-2015 05:35

Index is available for Country + State + City. So both the queries will use the same index.

Posted by Laura Stern on 16-Apr-2015 06:22

It seems clear to me, though without any hard evidence, that the combined query will be faster.  It will choose one index, say Country, since it's the first referenced, and then it will check on each record whether the other criteria match.  In the 2 query case it will read the same records again, and do some of the same criteria checking again.   The 2 query way sounds about twice as slow to me.

Posted by James Palmer on 16-Apr-2015 06:26

Completely agree with this evaluation. But as always, the best way to evaluate, is to check records read by the queries and see for yourself which is best.

Posted by Venky on 16-Apr-2015 06:43

thank you guys, will evaluate the # of reads on ProTop and let you know the results.

Posted by Venky on 16-Apr-2015 07:21

Hi James,

I have tried to install ProTop on Win& 32-bit but was not successful. Could you help me with this please?

Posted by Mike Fechner on 16-Apr-2015 07:23

Where are you struggling with installing ProTop? Any error message?

Posted by Venky on 16-Apr-2015 07:28

I downloaded ProTop3 as an msi. When I install I am asked for DB path, and installation path. Then installation rolls-back and simply sates that there was an error when installing and installation was not successful.

Posted by James Palmer on 16-Apr-2015 07:30

It's asking for the DLC path not the DB path. DLC is where Progress is installed.

Posted by Venky on 16-Apr-2015 07:36

okay, I was confused when it asked DLC for DB, this time installation is successful. I will keep you posted on the results.

Thanks all

Posted by ChUIMonster on 16-Apr-2015 07:53

Feel free to contact me directly if you have further questions.

Posted by S33 on 16-Apr-2015 09:30

If you have a single index on country, state, city, and ..., then i think it's safe to say the optimal performance will be joining with a temp-table.

def temp-table tt1 no-undo
    field mycity.

create tt1. tt1.mycity = 'new york'.
create tt1. tt1.mycity = 'buffalo'.

for each tt1,
    each cust where 
        cust.ctry = 'USA' and
        cust.state = 'NY' and = tt1.mycity

Posted by TheMadDBA on 16-Apr-2015 10:25

In your example A will be faster than B because it uses full equality matches for every indexed field. The example posted by S33 is much more elegant. If you include ctry and state in the temp-table it will also work perfectly for different combinations of ctry and city.

Posted by toomaskask on 16-Apr-2015 10:59

And why cannot OE compiler do the same when it detects such a situation?

Posted by TheMadDBA on 16-Apr-2015 11:11

Could it? Yes. Does it?. Nope.

I have asked several times over the years for a cost based optimizer for the 4GL and it doesn't look like that is happening  either.

We have to live with the rules we have, not the ones we want.

Posted by Thomas Mercer-Hursh on 16-Apr-2015 11:24

The key point being that ABL uses compile-time optimization not run-time.  That makes it easier to see what it is doing, but misses out on some opportunities to do better.

Posted by ujj1 on 17-Apr-2015 09:07

Assuming you have an index on Country, State, City, B will be slower because it won't use the city component of the index.  What we've been doing is:

for each customer where (country = "USA" and state = "NY" and city = "new york") or

                                              country = "USA" and state = "NY" and city = "buffalo") no-lock:

(or splitting it into two for each blocks like you did in a).

This thread is closed