Hi all,
I have a question concerning a query using BREAK BY statements.
It takes a relatively long time for the query to hit the first record it seems.
The query is:
The resultset in this is 600 records. The time it takes to hit the first record is ETIME 2800. The more records, the longer it takes to hit the first record.
e.g. ETIME for 570000 records = 220000.
The BREAK BY fields are all part of an index and are used in the sequence of the index sequence. Of course, when I omit the BREAK BY part the query hits the record in about 0 seconds.
I've tested this in OpenEdge 10.1C sp3 with a Progress db and an Oracle db.
Both displayed this behaviour. It has something to do with the BREAK BY and the index used. Can someone hint me in the right direction or is just standard Progress behaviour (sure don't hope so...)
Edit:
added new index debtrs_test on Progress db: adm-nr entry-nr debtor entry-nr-type seq-nr-deb (so this matches the WHERE clausule) and rearranged the BREAK BY sequence which now is:
BREAK BY debtrs.adm-nr BY debtrs.entry-nr BY debtrs.debtor BY debtrs.entry-nr-type BY debtrs.seq-nr-deb:
and added a USE-INDEX debtrs_test to the query. Result: 0 seconds! (resultset 200000 records). But this doesn't work on the ORACLE db. So I have to a little bit more digging on that one...
new query is:
I have a question concerning a query using BREAK BY statements.
It takes a relatively long time for the query to hit the first record it seems.
The query is:
Code:
ETIME(YES).
FOR EACH debtrs
WHERE debtrs.adm-nr = 100
AND debtrs.entry-nr >= 100
AND debtrs.entry-nr <= 200
NO-LOCK BREAK BY debtrs.adm-nr BY debtrs.debtor BY debtrs.entry-nr BY debtrs.entry-nr-type BY debtrs.seq-nr-deb:
MESSAGE string(ETIME) VIEW-AS ALERT-BOX.
END.
The resultset in this is 600 records. The time it takes to hit the first record is ETIME 2800. The more records, the longer it takes to hit the first record.
e.g. ETIME for 570000 records = 220000.
The BREAK BY fields are all part of an index and are used in the sequence of the index sequence. Of course, when I omit the BREAK BY part the query hits the record in about 0 seconds.
I've tested this in OpenEdge 10.1C sp3 with a Progress db and an Oracle db.
Both displayed this behaviour. It has something to do with the BREAK BY and the index used. Can someone hint me in the right direction or is just standard Progress behaviour (sure don't hope so...)
Edit:
added new index debtrs_test on Progress db: adm-nr entry-nr debtor entry-nr-type seq-nr-deb (so this matches the WHERE clausule) and rearranged the BREAK BY sequence which now is:
BREAK BY debtrs.adm-nr BY debtrs.entry-nr BY debtrs.debtor BY debtrs.entry-nr-type BY debtrs.seq-nr-deb:
and added a USE-INDEX debtrs_test to the query. Result: 0 seconds! (resultset 200000 records). But this doesn't work on the ORACLE db. So I have to a little bit more digging on that one...
new query is:
Code:
ETIME(YES).
FOR EACH debtrs USE-INDEX debtrs_test
WHERE debtrs.adm-nr = 100
AND debtrs.entry-nr >= 100
AND debtrs.entry-nr <= 200
NO-LOCK BREAK BY debtrs.adm-nr BY debtrs.entry-nr BY debtrs.debtor BY debtrs.entry-nr-type BY debtrs.seq-nr-deb:
MESSAGE string(ETIME) VIEW-AS ALERT-BOX.
END.