Question regarding nested for each

[Stuart Tinto]

I have a piece of code

for each table1
    where table1.company = 1
      and table1.deleted = false
      and table1.cancelled = false
      and table1.broken = false no-lock:
 
end.

Which takes around 5 seconds to run, however when I change it to:

for each table1
    where table1.company = 1
      and table1.deleted = false
      and table1.cancelled = false
      and table1.broken = false no-lock,
    first table2
    where table2.company = 1
      and table2.acc_id = table1.acc_id
      and table2.arr_id = table1.arr_id
      and table2.del = false no-lock:
 
end.

The run time jumps dramatically to around the 90 second mark.
the fields company, acc_id, arr_id and del on table2 are all single component indexes.

I am surprised at just how much longer the query takes as I would have thought that each itteration of the for each table1 loop would only have to resolve a single for first on table2

Any ideas or am I just missunderstanding how indexing should work?

Thanks in advance
Stuart

 

[TomBascom]

Setting aside performance for a moment -- FOR FIRST almost certainly isn't doing what you think it is doing. It is a very, very bad programming practice. What record are you expecting to be "first"? Based on what criteria? (There is nothing in your code that provides any hint -- so what do you think the compiler is doing?) How do you know that that is really what you're getting (it probably isn't -- FIRST has its own ideas on that and they are not what you think they are). What's so special about that record that it should be first? If it is first -- what about the 2nd and the third and so forth? Why aren't you looking at those records?

Next -- performance. Single component indexes are not a performance panacea. In fact they often have the effect that you are observing. If you put them in yourself I strongly suggest that you remove them before it is too late. If you are looking for performance instead create good multi-component indexes that match up with your expected WHERE clauses.

I suggest that compile your sample code with the XREF option and see what indexes are actually being used for selection and sorting. You might want to turn on client-logging and enable some of the query logging attributes too. You could also monitor index and table activity with ProTop when the query runs -- that will show you which ones are active and how much activity there is. I'll bet there is a lot more going on than you expect. Run the session with -t and observe the size of the SRT file. If it gets really large then you have client side sorting going on.

 

[GregTomkins]

Progress doesn't have a notion of statistics the way SQL does and it picks indices at compile time; given 4 single-component indices to choose from, and a query that involves 4 different fields, it has no way of knowing which one is best (even though it might be obvious to a human that, say, birth date is a much better selector than gender). You have to either (a) trick it into picking the right one; (b) use the USE-INDEX clause; or (c) add a multi-component index.

 

[Stefan]

Also beware that the nested notation does not result in a nested query as it does on SQL Server / Oracle. It is still evaluated as:

FOR EACH table1:
   FOR FIRST table2:
   END.
END.

One query is opened for table1 and for every single record of table1 a query on table2 is opened - each one forgetting entirely what the previous query on table2 had found.