CODEWITHSUNDEEP
progress-4glopenedge
user3580861
user3580861

Reputation: 13

For each loop for a table in OpenEdge 10.2b takes more time

Below for each loop takes more time and i cant able to trace index usage using XREF as the table uses Oracle Schema.Please Help me.

Need to generate report for different Report Type ( Report type is the input parameter in my code ).Single report type may contain more than 50,000 records how to access all the record within minute.Index detail also mentioned below for each loop.

FOR EACH Report 
      FIELDS(EXTRACTDATE STATUS MailingType ReportType ReportNumber 
             RequestID CustID)
      WHERE Report.EXTRACTDATE < Today
        AND Report.ReportType = 'Customer Report' 
        AND Report.STATUS = 'Pending' 
        AND (Report.MailingType  = "LETTER"
         OR Report.MailingType  = "Mail") NO-LOCK:

             < Statements >
                 .
                 .

END.

**Index Detail**

CREATE INDEX "TYPE_IDX1" ON "Report" ("EXTRACTDATE" DESC, "ReportTYPE", "STATUS", "MailingTYPE", "PROGRESS_RECID") 

CREATE INDEX "REQ_IDX1" ON "Report" ("REQUESTID", "PROGRESS_RECID") 

CREATE INDEX "RTTYP_IDX1" ON "Report" ("ReportTYPE","PROGRESS_RECID")

Upvotes: 1

Views: 888

Answers (1)

Tim Kuehn
Tim Kuehn

Reputation: 3251

The "OR" at the end will slow things down considerably - the AVM does better if you split it up into two sets of AND statements and OR the result, like so: 

WHERE (Report.EXTRACTDATE < Today
    AND Report.ReportType = 'Customer Report' 
    AND Report.STATUS = 'Pending' 
    AND Report.MailingType  = "LETTER") 

            OR 

      (Report.EXTRACTDATE < Today
    AND Report.ReportType = 'Customer Report' 
    AND Report.STATUS = 'Pending' 
    AND Report.MailingType  = "Mail")

Upvotes: 1

Related Questions