topic Re: Performance for the below code in Application Development and Automation Discussions

Performance for the below code

Former Member — Tue, 16 Jan 2007 16:15:58 GMT

Can any one help me in improving the performance for the below code.

FORM RETRIEVE_DATA .

CLEAR WA_TERRINFO.

CLEAR WA_KNA1.

CLEAR WA_ADRC.

CLEAR SORT2.

*To retrieve the territory information from ZPSDSALREP

SELECT ZZTERRMG

ZZSALESREP

NAME1

ZREP_PROFILE

ZTEAM

INTO TABLE GT_TERRINFO

FROM ZPSDSALREP.

*Preparing Corporate ID from KNA1 & ADRC and storing it in SORT2 field

LOOP AT GT_TERRINFO INTO WA_TERRINFO.

SELECT SINGLE * FROM KNA1 INTO WA_KNA1

WHERE KUNNR = WA_TERRINFO-SALESREP.

SELECT SINGLE * FROM ADRC INTO WA_ADRC

WHERE ADDRNUMBER = WA_KNA1-ADRNR.

IF NOT WA_ADRC-SORT2 IS INITIAL.

CONCATENATE 'U' WA_ADRC-SORT2 INTO SORT2.

MOVE SORT2 TO WA_TERRINFO-SORT2.

MODIFY GT_TERRINFO1 FROM WA_TERRINFO.

APPEND WA_TERRINFO TO GT_TERRINFO1.

CLEAR WA_TERRINFO.

ENDIF.

CLEAR WA_KNA1.

CLEAR WA_ADRC.

ENDLOOP.

ENDFORM. " RETRIEVE_DATA

Re: Performance for the below code

Former Member — Tue, 16 Jan 2007 16:22:34 GMT

The code is easy so I don't think you can do nothing, only u can try to limit the reading of KNA1:

FORM RETRIEVE_DATA .
  CLEAR WA_TERRINFO.
  CLEAR WA_KNA1.
  CLEAR WA_ADRC.
  CLEAR SORT2.
*To retrieve the territory information from ZPSDSALREP
  SELECT ZZTERRMG
  ZZSALESREP
  NAME1
  ZREP_PROFILE
  ZTEAM
  INTO TABLE GT_TERRINFO
  FROM ZPSDSALREP.

  SORT GT_TERRINFO BY SALESREP.

*Preparing Corporate ID from KNA1 & ADRC and storing it in SORT2 field
  LOOP AT GT_TERRINFO INTO WA_TERRINFO.

    IF KNA1-KUNNR <> WA_KNA1-KUNNR.
      SELECT SINGLE * FROM KNA1 INTO WA_KNA1
           WHERE KUNNR = WA_TERRINFO-SALESREP.
      IF SY-SUBRC <> 0.
        CLEAR: WA_KNA1, WA_ADRC.
      ELSE.
        SELECT SINGLE * FROM ADRC INTO WA_ADRC
                                 WHERE ADDRNUMBER = WA_KNA1-ADRNR.
        IF SY-SUBRC <> 0. WA_ADRC. ENDIF.
      ENDIF.
    ENDIF.

    IF NOT WA_ADRC-SORT2 IS INITIAL.
      CONCATENATE 'U' WA_ADRC-SORT2 INTO SORT2.
      MOVE SORT2 TO WA_TERRINFO-SORT2.
* MODIFY GT_TERRINFO1 FROM WA_TERRINFO.
      APPEND WA_TERRINFO TO GT_TERRINFO1.
      CLEAR WA_TERRINFO.
    ENDIF.
  ENDLOOP.

ENDFORM. " RETRIEVE_DATA

If program takes many times to upload the data from ZPSDSALREP, you can try to split in sevaral packages:

SELECT ZZTERRMG ZZSALESREP NAME1 ZREP_PROFILE ZTEAM
  INTO TABLE GT_TERRINFO PACKAGE SIZE <...> 
  FROM ZPSDSALREP.

  SORT GT_TERRINFO BY SALESREP.

*Preparing Corporate ID from KNA1 & ADRC and storing it in SORT2 field
  LOOP AT GT_TERRINFO INTO WA_TERRINFO.

    IF KNA1-KUNNR <> WA_KNA1-KUNNR.
      SELECT SINGLE * FROM KNA1 INTO WA_KNA1
           WHERE KUNNR = WA_TERRINFO-SALESREP.
      IF SY-SUBRC <> 0.
        CLEAR: WA_KNA1, WA_ADRC.
      ELSE.
        SELECT SINGLE * FROM ADRC INTO WA_ADRC
                                 WHERE ADDRNUMBER = WA_KNA1-ADRNR.
        IF SY-SUBRC <> 0. WA_ADRC. ENDIF.
      ENDIF.
    ENDIF.

    IF NOT WA_ADRC-SORT2 IS INITIAL.
      CONCATENATE 'U' WA_ADRC-SORT2 INTO SORT2.
      MOVE SORT2 TO WA_TERRINFO-SORT2.
* MODIFY GT_TERRINFO1 FROM WA_TERRINFO.
      APPEND WA_TERRINFO TO GT_TERRINFO1.
      CLEAR WA_TERRINFO.
    ENDIF.
  ENDLOOP.
ENDSELECT.

Max

Re: Performance for the below code

Former Member — Tue, 16 Jan 2007 16:25:47 GMT

take the select statement outside the loop.

SELECT kna1_{kunnr kna1}adrnr adrc~sort2

into table itab1

from kna1 join adrc

for all entries

on kna1_{adrnr = adrc}adrnr

for all entries in gt_terrinfo

where kna1~kunnr = gt_terrinfo-zzsalesrep.

LOOP AT GT_TERRINFO INTO WA_TERRINFO.

read table itab1 into s_itab

with key kunnr = wa_terrinfo-zzsalesrep.

use the S_ITAB-sort2 and the logic you have written

ENDLOOP.

Re: Performance for the below code

Former Member — Tue, 16 Jan 2007 16:27:00 GMT

Hi,

Move the SQL for KNA1 & ADRC outside the loop.

Check the changes..

DATA: BEGIN OF ITAB_KNA1 OCCURS 0,

KUNNR TYPE KUNNR,

ADRNR TYPE KNA1-ADRNR,

END OF ITAB_KNA1.

DATA: BEGIN OF ITAB_ADRC OCCURS 0,

ADRNR TYPE ADRC-ADRNR,

SORT2 TYPE ADRC-SORT2,

END OF ITAB_ADRC.

IF NOT GT_TERRINFO[] IS INITIAL.

SELECT KUNNR ADRNR INTO TABLE ITAB_KNA1

FROM KNA1

FOR ALL ENTRIES IN GT_TERRINFO WHERE

KUNNR = GT_TERRINFO-SALESREP.

IF SY-SUBRC = 0.

SORT ITAB_KNA1 BY KUNNR.

ENDIF.

IF NOT ITAB_KNA1[] IS INITIAL.

SELECT ADRNR SORT2 INTO TABLE ITAB_ADRC

FROM ADRC

FOR ALL ENTRIES IN ITAB_KNA1

WHERE ADRNR = ITAB_KNA1-ADRNR.

IF SY-SUBRC = 0.

SORT ITAB_ADRC BY ADRNR.

ENDIF.

ENDIF.

LOOP AT GT_TERRINFO INTO WA_TERRINFO.

CLEAR: ITAB_KNA1, ITAB_ADRC.

READ TABLE ITAB_KNA1 WITH KEY

KUNNR = WA_TERRINFO-SALESREP

BINARY SEARCH.

READ TABLE ITAB_ADRC WITH KEY

ADRNR = ITAB_KNA1-ADRNR

BINARY SEARCH.

IF NOT ITAB_ADRC-SORT2 IS INITIAL.

CONCATENATE 'U' ITAB_ADRC-SORT2 INTO SORT2.

MOVE SORT2 TO WA_TERRINFO-SORT2.

MODIFY GT_TERRINFO1 FROM WA_TERRINFO.

APPEND WA_TERRINFO TO GT_TERRINFO1.

CLEAR WA_TERRINFO.

ENDIF.

CLEAR WA_KNA1.

CLEAR WA_ADRC.

ENDLOOP.

Thanks,

Naren

Re: Performance for the below code

bill_kroll — Tue, 16 Jan 2007 16:31:45 GMT

Move your selects out of loop then do read binary.

Psuedo code

check not gt_terrinfo[] is initial.

Select ...... into table gt_kna1 from kna1 for all entries in gt_terrinfo where....

if not gt_kna1[] is initial.

sort gt_kna1 by kunnr.

select ...... into table gt_adrc from adrc for all entries in gt_kna1 where.

endif.

sort: gt_adrc by addrnumber.

loop at gt_terrinfo assigning <fs>

read table gt_kna1 with key kunnr = <fs>-kunnr binary search.

if sy-subrc eq 0.

read table gt_adrc with key addrnumber = <fs>-addrnumber binary search.

if sy-subrc eq 0.

not sure where you trying to do here, but you keep appending to the table

you are looping at?

CONCATENATE 'U' GT_ADRC-SORT2 INTO SORT2.

MOVE SORT2 TO <fs>-SORT2. "No need to modify if you using field symbol

endif

endloop.

Re: Performance for the below code

Former Member — Tue, 16 Jan 2007 16:42:50 GMT

What percentage of KNA1 do you expect to read each time the report is run?

Rob

Re: Performance for the below code

Former Member — Tue, 16 Jan 2007 17:18:27 GMT

Satya - I appreciate the points, but I'm not sure how you determined that I solved your question.

Rob

Well anyway, my point was going to be that if you need to select data from a significant portion of the database, it may be more advantageous to select the entire database and then do binary searches against it when you need the data.

The definition of 'significant' would depend on a number of factors but would be in the range of more than 1/4 of the database and perhaps as much as 3/4.

Message was edited by:

Rob Burbank