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:
....
....
end.
for each customer where country = "USA" and state = "NY" and city = "buffalo" no-lock:
....
....
end.
b) for each customer where country = "USA" and state = "NY" and (city = "new york" or city = "buffalo") no-lock:
....
....
end.
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.
Index is available for Country + State + City. So both the queries will use the same index.
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.
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.
thank you guys, will evaluate the # of reads on ProTop and let you know the results.
Hi James,
I have tried to install ProTop on Win& 32-bit but was not successful. Could you help me with this please?
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.
It's asking for the DLC path not the DB path. DLC is where Progress is installed.
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
Feel free to contact me directly if you have further questions.
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
cust.city = tt1.mycity
no-lock:
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.
And why cannot OE compiler do the same when it detects such a situation?
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.
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.
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).