Can the use of the OF keyword cause Delays?

[oscarse]

The system that is running Progress 9x is a super fast AIX box so it bothers me when I see random several second delays for tables that are assumed to be properly indexed. I recognize that I can change the FE in the example to use an explicit WHERE clause here ... I am however hoping that someone can confirm that the OF clause is in fact responsible for these types of delays? NOTE tbl_two (table of items) is huge ... I have found it extremely difficult to prove this issue because the AIX system has a large CACHE ... the moment I rerun any code, AIX must be getting its data from the CACHE which normally negates any code/db fetch delays ... basically the first instance of a delay can't be reproduced. It sure would be nice if I could force Progress to always fetch DATA from DISK just for testing.

FIND FIRST tbl_one USE-INDEX primary-index.

FOR EACH tbl_two OF tbl_one. /* is this code potentially
causing a seek of all
records in the db when there
is no matching tbl_two rec?
*/
DELETE tbl_two.
END.


tbl_one
FIELD ref-num AS INTEGER
FIELD ... header fields
INDEX primary-index AS PRIMARY
ref-num ASCENDING.

tbl_two
FIELD ref-num AS INTEGER
FIELD ... item fields
INDEX primary-index AS PRIMARY
ref-num ASCENDING.

 

[HelloMike]

In the example you give, there should be no difference in performance between OF and WHERE. However, I suspect this is a much simplified example of your real-world case. If you compile your offending program with XREF it will tell you what index(es) your OF is actually using. If it's not what you expect then you've found the cause of your problem, otherwise the problem lies elsewhere.

Cheers, Mike.

 

[piperent]

To the best of my knowledge the 'OF' condition is converted to a 'WHERE' by Progress.

The simple indexing of a file in no way guarantees fast execution of a query. It only provides for the improved acquisition of data. File size, index complexity, component construction, and many other details should be considered when determining the functional success of a routine. Yes, they can be excrutiatingly slow.

ALL DBMS applications have to deal with these dynamics. They are not exclusive to any one platform - PROGRESS, SQL, ORACLE, INFORMIX, and the list goes on.

JP