Speedup For Each Code

kasundha

Member
Hi,

I have a openedge code and it gets more than 10 hours to run.

first table has 40K + records and other tables have about 5 records per each record of first table. can you give me any suggestion to improve the efficiency of this code.

--------------------
Code:
FOR EACH corpdata.ArrearsRpt FIELDS() WHERE NOT CAN-DO("CC,SE,SM",SUBSTR(corpdata.ArrearsRpt.facno,5,2))
                                            AND corpdata.ArrearsRpt.sysDte = this-monthend
                                            AND ArrearsRpt.recSts <> "R"
                                            AND ArrearsRpt.noRntArr >= 2
                                            AND corpdata.ArrearsRpt.recOffc = t-officer.t-ofcCode
                                            AND NOT can-do("IN251870,IL063128,IA065464,IL070155",corpdata.ArrearsRpt.recOffc)
                                            AND NOT CAN-FIND(FIRST corpdata.AFI_FORM_UPDATER WHERE corpdata.AFI_FORM_UPDATER.FACILITY_NO = corpdata.ArrearsRpt.facno
                                                                        AND (int(substr(corpdata.AFI_FORM_UPDATER.ACTION_DATE,1,4)) = YEAR(mSYSDATE)
                                         AND int(SUBSTR(corpdata.AFI_FORM_UPDATER.ACTION_DATE,6,2)) = MONTH(mSYSDATE)
                                         AND (int(SUBSTR(corpdata.AFI_FORM_UPDATER.ACTION_DATE,9,2)) > 1 AND int(SUBSTR(corpdata.AFI_FORM_UPDATER.ACTION_DATE,9,2)) < DAY(mSYSDATE) - 0))) NO-LOCK:
        ASSIGN this-not-visitted = this-not-visitted + 1.
        FIND FIRST DUE_RCPT WHERE DUE_RCPT.FACNO = corpdata.ArrearsRpt.facno
                                AND SUBSTRING (DUE_RCPT.rcptno,5,1) <> "C"
                                AND SUBSTRING (DUE_RCPT.rcptno,5,1) <> "W"
                                AND due_rcpts.amtstld <> ?
                                AND (DUE_RCPT.STLDATE <= mSYSDATE)
                                AND  DUE_RCPT.STLDATE >= this-monthend
                                AND NOT(CAN-FIND(FIRST gl.receipts WHERE gl.receipts.rcptno =  DUE_RCPT.rcptno AND gl.receipts.descr      = "Sale of Vahicle Arrears Transfer to Reposess A/C"  ))  NO-LOCK NO-ERROR.
        IF AVAILABLE DUE_RCPT THEN
        DO:
            ASSIGN this-not-visitted-paid = this-not-visitted-paid + 1.
        END.
        STATUS INPUT "PROCESS NOT VISITED - " + string(t-id) + " - " + string(this-not-visitted ).
       
    END.
 
Last edited by a moderator:

KrisM

Member
You could start with giving us some info on which indexes are defined on the database tables that you access in your code.
 

Osborne

Active Member
First thing that instantly stands out is you are using CAN-DO which should not be used at all. Tom 's posting explains it well:


As well as the CAN-DO lines, you have other functions - INT, SUBSTR - on other lines which will cause table scans:


Also lines such as ArrearsRpt.recSts <> "R" are resulting in all records being read to find all the records not equal to R. Replace with something like:

Code:
ArrearsRpt.recSts < "R" OR ArrearsRpt.recSts > "R"

Finally, as Kris asks, does a relevant index exist that has all the fields you are accessing.
 
Hi
It is a little bit difficult without the index

But You should/could:
- replace the CAN-DO by LOOKUP .
- (int(substr(corpdata.AFI_FORM_UPDATER.ACTION_DATE,1,4)) = YEAR(mSYSDATE) By corpdata.AFI_FORM_UPDATER.ACTION_DATE Begins String(YEAR(mSYSDATE) , "9999" ) (and perhaps By corpdata.AFI_FORM_UPDATER.ACTION_DATE Begins String(YEAR(mSYSDATE) , "9999" ) + "/" +String(Month(mSYSDATE) , "99" )

Patrice
 

TomBascom

Curmudgeon
In addition to the issues with CAN-DO pointed out in the link above it is very important to note that because CAN-DO is a *security* function it is *always* evaluated on the client. So when you use it in a WHERE clause you not only create a TABLE-SCAN, you also force all of the records to be sent to the client for selection. Other functions (such as LOOKUP) can be evaluated on the server.
 

TomBascom

Curmudgeon
General query construction advice: Start with positive statements about equality. Try to find ways to say that a field *equals* a value rather than expressing exceptions and filters.

The more equality matches you can specify the better your query will perform.

For instance, it is *much* better to code:

WHERE f = "a" or f = "b" or f ="d"

than it is to code:

WHERE f <> "c"

or:

WHERE lookup( f, "a,b,d" ) > 0

If you *can* enumerate all of the positive equality matches you should. If there are a large number then it may be helpful to create an outer loop with a temp table or comma delimited list. For example:
Code:
keyList = "a,b,d".
n = num-entries( keyList ).
do i = 1 to n:
  for each table no-lock where table.f = entry( i, keyList ):
  end.
end.

One possible example of that is your recOffc field. I'm just guessing but that looks like a list of office codes. If there us a master list in a table somewhere, maybe called mstrOffc then you could iterate through the *valid* office codes in an outer loop (see below).

It isn't strictly necessary but placing the equality matches in the first portion of a complex WHERE clause makes it a lot easier to see if there are indexes which can be used to support the query.

The single most important index selection rule is to create equality matches on as many leading components of an index as possible.

If your WHERE clause has all sorts of functions and inequalities in it then you may as well move all of that junk out of the WHERE and into the body of the FOR EACH. You're doing a table scan anyway, so make it cleaner to follow your logic. For your query above you might go with something along these lines:

Code:
for each mstrOffc no-lock where lookup( mstrOffc.recOffc, "IN251870,IL063128,IA065464,IL070155" ) = 0:

  FOR EACH corpdata.ArrearsRpt NO-lOCK WHERE
               corpdata.ArrearsRpt.sysDte = this-monthend
  AND corpdata.ArrearsRpt.recOffc = t-officer.t-ofcCode
  AND corpdata.ArrearsRpt.recOffc = mstrOffc.recOffc
  AND ArrearsRpt.noRntArr >= 2:

    if lookup( SUBSTR(corpdata.ArrearsRpt.facno,5,2), "CC,SE,SM" ) = 0 then next.
    if ArrearsRpt.recSts <> "R" then next.  /* this could also be an outer loop iterating through "recSts" values that you want rather than excluding the one that you do not want. */
    if NOT CAN-FIND( FIRST corpdata.AFI_FORM_UPDATER NO-LOCK WHERE corpdata.AFI_FORM_UPDATER.FACILITY_NO = corpdata.ArrearsRpt.facno then next.
    /* ... */
     
  END.

end.

This makes it a *lot* more obvious which fields are usefully helping to resolve the query and which are just dead wood making it complicated and difficult to understand.
 

andre42

Member
Great post as always, Tom.
If you look more closely both lines
Code:
AND corpdata.ArrearsRpt.recOffc = t-officer.t-ofcCode
AND NOT can-do("IN251870,IL063128,IA065464,IL070155",corpdata.ArrearsRpt.recOffc)
refer to corpdata.ArrearsRpt.recOffc.
kasundha should check t-officer.t-ofcCode against the list before entering the for each.

But the main issue is certainly that there are only very few equality matches.
 
Top