Query performance (BREAK BY)

[palthe]

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:

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:

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.

 

[TomBascom]

In your first example you were breaking by a field that wasn't in the index. That basically requires the whole result set to be returned and then sorted by the client.

You fixed that by adding the missing field to the index.

Did you add the revised index to the Oracle db as well?

In addition Oracle uses a run-time cost-based query optimizer (Progress uses a compile-time static query optimizer). You will have to tell Oracle to update its statistics and you may have to give it an index hint.

 

[palthe]

In your first example you were breaking by a field that wasn't in the index. That basically requires the whole result set to be returned and then sorted by the client.

You fixed that by adding the missing field to the index.

Did you add the revised index to the Oracle db as well?

In addition Oracle uses a run-time cost-based query optimizer (Progress uses a compile-time static query optimizer). You will have to tell Oracle to update its statistics and you may have to give it an index hint.

Thanks! Yeah I needed the Progress side to work, just to be sure the query I used was right. The Oracle thing will be solved!