strange performance problem with parallel loops

[Former Member]

I have implemented parallel loops to increase my performance and it's working fine. however I have to do some additional acctions to prepare the code since the loop crashes when the key in the second table does not exist in the first table from the loop.

my performance has increased dramaticaly when I looped through 40.000 x 10.000 records. but when I wanted to improve all the selections even more I also made a selection on the second loop which contains ordernumbers so only open orders would be in the table and not closed orders. so the amount is 40.000 x 3.000

but now the performance dropped even below the times with the old fashioned way

can somebody explain which caused this drop in performance ?

 it_prpsaufnrs[] = it_prpsrapp[].
    sort it_prpsaufnrs by aufnr.
    DELETE ADJACENT DUPLICATES FROM it_prpsaufnrs
    COMPARING aufnr .

  SORT it_prpsaufnrs BY aufnr.
  SORT it_catsdbtot BY raufnr.

  catstot_index = 1.
  prpsrapp_index = 1.
*remove ordernummers which are not in it_prpsrapp 
  LOOP AT it_catsdbtot INTO wa_catsdbtot.
    LOOP AT it_prpsaufnrs INTO wa_prpsaufnrs FROM prpsrapp_index.
      IF wa_prpsaufnrs-aufnr = wa_catsdbtot-raufnr.
        prpsrapp_index = sy-tabix.
        h_exist = 'X'.
        EXIT.
      ELSE.
        h_exist = ''.
      ENDIF.

    ENDLOOP.

    IF h_exist = 'X'.
      APPEND wa_catsdbtot TO it_catsdbtot2.
    ENDIF.
  ENDLOOP.

  SORT it_prpsrapp BY pernr_cats aufpl aplzl.
  SORT it_catsdbtot2 BY pernr raufpl raplzl.

  LOOP AT it_prpsrapp.
    it_prpsrapp-totaal = 0.
*ads 19-07-2006 nieuwe poging performance verbetering parralel_cursor
    LOOP AT it_catsdbtot2 INTO wa_catsdbtot FROM catstot_index.
      IF wa_catsdbtot-pernr <> it_prpsrapp-pernr_cats
         OR wa_catsdbtot-raufpl <> it_prpsrapp-aufpl
         OR wa_catsdbtot-raplzl <> it_prpsrapp-aplzl.
        catstot_index = sy-tabix.
        EXIT.
      ELSE.
        it_prpsrapp-totaal = it_prpsrapp-totaal + wa_catsdbtot-catshours.

      ENDIF.

    ENDLOOP.
    MODIFY it_prpsrapp.
 ENDLOOP.

or if somebody has a good idea to make the parralel loop more monkey proof so it won't crash when the second table contains keys which the main table doesn't have . that would also be nice

kind regards

arthur

Message was edited by:

A. de Smidt

[Former Member]

You still have nested loops. Check:

/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops

Rob

[Former Member]

@Rob as you can see I use the same code as mentioned in the Log you mentioned.

but why is 40.000 x 10.000 30 times faster than 40.000 x 3.000 in the above code ??

[Former Member]

Now that I've looked at it a bit more, that may not be the problem. I'll look at it again.

Rob

[Former Member]

Is the problem with the first loop where you remove the lines or the second one?

Rob

[Former Member]

Hmmm I will look into it tomorrow . Is there a easy way to determine which loop causes the problem without having to make 2 forms out of it ??

[Former Member]

I think I found it. Try:

catstot_index = 1.
prpsrapp_index = 1.
*remove ordernummers which are not in it_prpsrapp
LOOP AT it_catsdbtot INTO wa_catsdbtot.
  LOOP AT it_prpsaufnrs INTO wa_prpsaufnrs FROM prpsrapp_index.
    CLEAR h_exist.
    IF wa_prpsaufnrs-aufnr = wa_catsdbtot-raufnr.
      prpsrapp_index = sy-tabix.
      h_exist = 'X'.
    ENDIF.
    EXIT.
  ENDLOOP.

Rob

[Former Member]

A cleaner and easier to read way would be:

*remove ordernummers which are not in it_prpsrapp
LOOP AT it_catsdbtot INTO wa_catsdbtot.
  READ TABLE it_prpsaufnrs INTO wa_prpsaufnrs
    WITH KEY aufnr = it_catsdbtot-raufnr
    BINARY SEARCH.
  IF sy-subrc = 0.
    APPEND wa_catsdbtot TO it_catsdbtot2
    endif.
  ENDLOOP.

I haven't tested either of these, but the problem seems to be that your original loop kept going after finding a hit.

Rob

[Former Member]

Yep I also got the feeling that something was wrong in the loop

this is working fantastic.

ps if you ever find a nice sollution for the problem of additional keys in the table inside the loop it would be nice to hear

I didn't check the index procedure for doing nested loops but has it the same problem ??

[Former Member]

You can use the parallel cursor methd to loop through both tables simultaneously. The coding is kind of tricky though. You have to keep track of two sets of keys.

Rob