topic Re: strange performance problem with parallel loops in Application Development and Automation Discussions

strange performance problem with parallel loops

Former Member — Wed, 20 Jun 2007 13:48:59 GMT

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

Re: strange performance problem with parallel loops

Former Member — Wed, 20 Jun 2007 13:57:23 GMT

You still have nested loops. Check:

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

Rob

Re: strange performance problem with parallel loops

Former Member — Wed, 20 Jun 2007 14:18:58 GMT

@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 ??

Re: strange performance problem with parallel loops

Former Member — Wed, 20 Jun 2007 14:19:18 GMT

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

Rob

Re: strange performance problem with parallel loops

Former Member — Wed, 20 Jun 2007 14:21:18 GMT

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

Rob

Re: strange performance problem with parallel loops

Former Member — Wed, 20 Jun 2007 14:30:24 GMT

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 ??

Re: strange performance problem with parallel loops

Former Member — Wed, 20 Jun 2007 14:34:17 GMT

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

Re: strange performance problem with parallel loops

Former Member — Wed, 20 Jun 2007 14:44:22 GMT

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

Re: strange performance problem with parallel loops

Former Member — Thu, 21 Jun 2007 14:33:40 GMT

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 ??

Re: strange performance problem with parallel loops

Former Member — Thu, 21 Jun 2007 16:02:55 GMT

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