Better code for fallowing code

[Former Member]

BEGIN OF y_mara,
       matnr TYPE matnr,  "Material number
       mtart TYPE mtart,  "Material type
       meins TYPE meins,  "Base Unit of Measure
       END OF y_mara,
wt_mara              TYPE TABLE OF y_mara,

BEGIN OF y_afko,
       aufnr  TYPE aufnr,     "Order Number
       gltrp  TYPE co_gltrp,  "Basic finish date
       gamng  TYPE gamng,     "Total order quantity
       plnbez TYPE matnr,     "Material Number
       plnty  TYPE plnty,     "Task List Type
       plnnr  TYPE plnnr,     "Key for Task List Group
       plnal  TYPE plnal,     "Group Counter
       END OF y_afko,

wt_afko TYPE HASHED TABLE of y_afko
       with unique key AUFNR,

   SELECT matnr     "Material number
         mtart     "Material type
         meins     "Base Unit of Measure
         FROM mara
         INTO TABLE p_wt_mara
         %_HINTS ORACLE 'INDEX("MARA" "MARA~T")'.

need help for below code to improve performance for AFKO selection other than innerjoin with forall entries is there any way to achive better performance

IF p_wt_mara[] IS NOT INITIAL.

    SELECT A~aufnr   "Order Number
           A~gltrp   "Basic finish date
           A~gamng   "Total order quantity
           A~plnbez  "Material Number
           A~plnty   "Task List Type
           A~plnnr   "Key for Task List Group
           A~plnal   "Group Counter
           FROM afko as A
           JOIN aufk as B ON A~aufnr = B~aufnr
           INTO TABLE p_wt_afko
           FOR ALL ENTRIES IN p_wt_mara
           WHERE A~plnbez EQ p_wt_mara-matnr and B~bukrs = p_bukrs.

    IF sy-subrc EQ 0.
      SORT p_wt_afko BY aufnr.
    ENDIF.
  ENDIF.

[former_member195270]

Hi Ravi,

Use following code.

PARAMETERS p_bukrs TYPE bukrs.

TYPES: BEGIN OF y_mara,

       matnr TYPE matnr,  "Material number

       mtart TYPE mtart,  "Material type

       meins TYPE meins,  "Base Unit of Measure

       END OF y_mara.

data: lt_mara  TYPE TABLE OF y_mara,

      ls_mara like LINE OF lt_mara.



TYPES: BEGIN OF y_afko,

       aufnr  TYPE aufnr,     "Order Number

       gltrp  TYPE co_gltrp,  "Basic finish date

       gamng  TYPE gamng,     "Total order quantity

       plnbez TYPE matnr,     "Material Number

       plnty  TYPE plnty,     "Task List Type

       plnnr  TYPE plnnr,     "Key for Task List Group

       plnal  TYPE plnal,     "Group Counter

       END OF y_afko.



data:  lt_afko TYPE HASHED TABLE of y_afko

       with unique key AUFNR,

       t_afko TYPE STANDARD TABLE OF y_afko,

       ls_afko type y_afko.



   SELECT matnr     "Material number

         mtart     "Material type

         meins     "Base Unit of Measure

         FROM mara

         INTO TABLE lt_mara

         %_HINTS ORACLE 'INDEX("MARA" "MARA~T")'.



IF lt_mara[] IS NOT INITIAL.



    SELECT A~aufnr   "Order Number

           A~gltrp   "Basic finish date

           A~gamng   "Total order quantity

           A~plnbez  "Material Number

           A~plnty   "Task List Type

           A~plnnr   "Key for Task List Group

           A~plnal   "Group Counter

           FROM afko as A

           JOIN aufk as B ON A~aufnr = B~aufnr

           INTO TABLE lt_afko

           WHERE B~bukrs = p_bukrs.



    loop at lt_mara INTO ls_mara.

      READ TABLE lt_afko INTO ls_afko

         with key plnbez = ls_mara-matnr.

      IF sy-subrc EQ 0.

        APPEND ls_afko to t_afko.

      ENDIF.



    endloop.



    IF lines( t_afko[] ) gt 0.

      SORT t_afko BY aufnr.

    ENDIF.



  ENDIF.

[hendrik_brandes]

Hello,

if possible, try to integrate the selection of mara into your join, therefore all selections runs on the database.

Use ORDER BY at the select of afko, so the database sort your resultset.

Another point: The table mara should be very huge. If possible, try to select a subset.

Kind regards,

Hendrik

[Former Member]

The code given by Omar is good . If you use this , u can also consider these points

1. Clear ls_afko immediately after append statement

2. Clear work area immediately after Endloop

3. Sorting of internal table as above is perfect, now use internal table loop with binary search

4. After activating the program , give it an EPC check

5. Try not using obsolete statements

6. Clear variables which are not required anymore

7. Use parallel cursor processing tech if using loop within a loop

8. Lastly , use ST05 to monitor load and performance

Plz reward points if it helps and revert in case of issues

Regards

Vivek

[former_member491621]

Hi Ravi,

Much performance cannot be expected from the select query you have on AFKO and AUFK as you are fetching values from these tables on non-key fields.

Umar's suggestion might increase performance(also with the use of BINARY SEARCH in the read).

Check if you could any secondary indexes in your query.

[Former Member]

Hello Guys,

Thanks for your tips here.

Hendrik,

I have tried with fallowing 3 types of code based on your tips but seems 2nd and 3rd block are not giving exact results like 1st block of code.

Source code1.Main code:Bad performance but correct records:

   IF p_wt_mara[] IS NOT INITIAL.

    SELECT A~aufnr   "Order Number
           A~gltrp   "Basic finish date
           A~gamng   "Total order quantity
           A~plnbez  "Material Number
           A~plnty   "Task List Type
           A~plnnr   "Key for Task List Group
           A~plnal   "Group Counter
           FROM afko as A
           JOIN aufk as B ON A~aufnr = B~aufnr
           INTO TABLE p_wt_afko
           FOR ALL ENTRIES IN p_wt_mara
           WHERE A~plnbez EQ p_wt_mara-matnr and B~bukrs = p_bukrs.

Source code2.Alternative one giving good performance but not giving exact records like Source code1.

    SELECT A~aufnr   "Order Number
               A~gltrp   "Basic finish date
               A~gamng   "Total order quantity
               A~plnbez  "Material Number
               A~plnty   "Task List Type
               A~plnnr   "Key for Task List Group
               A~plnal   "Group Counter
               FROM afko as A
               JOIN aufk as B ON A~aufnr = B~aufnr
               INTO TABLE p_wt_afko
      WHERE EXISTS ( SELECT * FROM MARA WHERE MATNR = A~plnbez )
      AND B~bukrs = p_bukrs.

Source code3.second Alternative one giving good performance but not giving exact records like Source code1

    SELECT A~aufnr   "Order Number
               A~gltrp   "Basic finish date
               A~gamng   "Total order quantity
               A~plnbez  "Material Number
               A~plnty   "Task List Type
               A~plnnr   "Key for Task List Group
               A~plnal   "Group Counter
               FROM afko as A
               JOIN aufk as B ON A~aufnr = B~aufnr
               JOIN mara as c ON  A~plnbez =  C~MATNR
               INTO TABLE p_wt_afko
       WHERE C~MTART NOT IN wt_mtart_range
      AND B~bukrs = p_bukrs
       %_HINTS ORACLE 'INDEX("MARA" "MARA~T")'.

    IF sy-subrc EQ 0.
      SORT p_wt_afko BY aufnr.
    ENDIF.

ENDIF.

can any one suggect me to modify Source code2 and Source code3 so that they will give correct records like Source code1.

Thanks

Ravi

[former_member491621]

Hi Ravi,

You could try changing your query like below

SELECT A~aufnr   "Order Number
               A~gltrp   "Basic finish date
               A~gamng   "Total order quantity
               A~plnbez  "Material Number
               A~plnty   "Task List Type
               A~plnnr   "Key for Task List Group
               A~plnal   "Group Counter
               FROM afko as A
               JOIN aufk as B ON A~aufnr = B~aufnr
               INTO TABLE p_wt_afko
      WHERE matnr IN ( SELECT matnr FROM MARA WHERE MATNR = <your condition> )
      AND B~bukrs = p_bukrs.

Also, you can try using the below code

DATA : w_tabix TYPE sytabix.

IF p_wt_mara[] IS NOT INITIAL.

    SELECT A~aufnr   "Order Number

           A~gltrp   "Basic finish date

           A~gamng   "Total order quantity

           A~plnbez  "Material Number

           A~plnty   "Task List Type

           A~plnnr   "Key for Task List Group

           A~plnal   "Group Counter

           FROM afko as A

           JOIN aufk as B ON A~aufnr = B~aufnr

           INTO TABLE p_wt_afko

           WHERE B~bukrs = p_bukrs.

SORT p_wt_afko ASCENDING BY plnbez.

LOOP AT p_wt_mara INTO wa_mara.

     READ TABLE p_wt_afko WITH KEY plnbez = wa_mara-matnr BINARY SEARCH TRANSPORTING NO FIELDS.

     IF sy-subrc EQ 0.

      w_tabix = sytabix.

      LOOP AT p_wt_afko INTO wa_AFKO FROM w_tabix.

      IF wa_afko-plnbez NE wa_mara-matnr.

           EXIT.

      ENDIF.

        APPEND wa_afko to t_afko.              (T_AFKO is another table of type Y_AFKO)

      ENDLOOP.

    ENDIF.

ENDLOOP.

ENDIF.

I expect it would give you your required result set.

Hope this helps

[Former Member]

Hi Guys,

Thanks for the help regarding performance issue.

Once again thanks for every one specially

Umar Abdullah.

I have tried with diff codes

1.select+For all+read

2.Inner+Parallel cursor

3.Inner+Read

looks like 3 one is giving good results.

see the fallowing code.

   *&---------------------------------------------------------------------*
*& Report  ZPERFORMANCETEST
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  ZPERFORMANCETEST.
*&---------------------------------------------------------------------*

*REPORT  ZPERFORMANCETEST.
types: BEGIN OF y_afko,
       aufnr  TYPE aufnr,     "Order Number
       gltrp  TYPE co_gltrp,  "Basic finish date
       gamng  TYPE gamng,     "Total order quantity
       plnbez TYPE matnr,     "Material Number
       plnty  TYPE plnty,     "Task List Type
       plnnr  TYPE plnnr,     "Key for Task List Group
       plnal  TYPE plnal,     "Group Counter
       END OF y_afko.

types:     BEGIN OF y_mara,
     matnr TYPE matnr,  "Material number
     mtart TYPE mtart,  "Material type
     meins TYPE meins,  "Base Unit of Measure
     END OF y_mara.

types:    BEGIN OF y_range,
    sign(1)    TYPE c,
    option(2)  TYPE c,
    low        TYPE char18,
    high       TYPE char18,
    END OF y_range.

DATA: wt_afko TYPE HASHED TABLE of y_afko
      with unique key AUFNR.
DATA:  wt_mara   TYPE TABLE OF y_mara.

data: t_afko TYPE STANDARD TABLE OF y_afko,
    lt_afko  TYPE STANDARD TABLE OF y_afko,
    ls_afko type y_afko,
    ls_mara type y_mara.
DATA:wt_mtart_range       TYPE TABLE OF y_range.

TYPES: BEGIN OF y_aufk,
        aufnr  TYPE aufnr, "Order Number
        bukrs  TYPE  bukrs,"Company code
       END OF y_aufk.
data: t_aufk TYPE hashed table of y_aufk
      with unique key AUFNR.


DATA: lv_flag TYPE flag,
lv_sta_time TYPE timestampl,
lv_end_time TYPE timestampl,
lv_diff_w   TYPE p DECIMALS 5,
lv_diff_f   LIKE lv_diff_w,
lv_save     LIKE lv_diff_w.
data:nr_records type n.

DATA : w_tabix TYPE sytabix.


SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-011.

PARAMETERS: p_bukrs TYPE bukrs OBLIGATORY,
            p_werks TYPE werks_d OBLIGATORY.

SELECTION-SCREEN END OF BLOCK b1.

CLEAR: lv_flag,
       lv_sta_time,
       lv_end_time.
clear:nr_records .


SELECT matnr     "Material number
     mtart     "Material type
     meins     "Base Unit of Measure
     FROM mara
     INTO TABLE wt_mara
     %_HINTS ORACLE 'INDEX("MARA" "MARA~T")'..
*         WHERE mtart IN wt_mtart_range.
IF sy-subrc EQ 0.
  SORT wt_mara BY matnr.
  delete wt_mara  WHERE mtart NOT IN wt_mtart_range.
ENDIF.

**************************************************
* Begin - Processing with Work area
GET TIME STAMP FIELD lv_sta_time.

IF wt_mara[] IS NOT INITIAL.

  SELECT A~aufnr   "Order Number
         A~gltrp   "Basic finish date
         A~gamng   "Total order quantity
         A~plnbez  "Material Number
         A~plnty   "Task List Type
         A~plnnr   "Key for Task List Group
         A~plnal   "Group Counter
         FROM afko as A
         JOIN aufk as B ON A~aufnr = B~aufnr
         INTO TABLE wt_afko
         FOR ALL ENTRIES IN wt_mara
         WHERE A~plnbez EQ wt_mara-matnr and B~bukrs = p_bukrs.

  IF sy-subrc EQ 0.
    SORT wt_afko BY aufnr.
  ENDIF.
ENDIF.

describe table wt_afko lines nr_records .
write:nr_records.
GET TIME STAMP FIELD lv_end_time.
lv_diff_w = lv_end_time - lv_sta_time.
WRITE: /(15) 'Inner+Forall', lv_diff_w.
* End   - Processing with Work Area
**********************************************************************
REFRESH:t_afko.
REFRESH:lt_afko.
REFRESH:wt_afko.
clear:nr_records .
CLEAR: lv_flag,
       lv_sta_time,
       lv_end_time.
* Begin - Processing with Field-Symbols
GET TIME STAMP FIELD lv_sta_time.
select aufnr from aufk into table t_aufk where bukrs = p_bukrs.
IF t_aufk[] IS NOT INITIAL.
  select aufnr   "Order Number
           gltrp   "Basic finish date
             gamng   "Total order quantity
             plnbez  "Material Number
             plnty   "Task List Type
            plnnr   "Key for Task List Group
             plnal   "Group Counter
            FROM afko into table t_afko
    for all entries in t_aufk[]
            where aufnr = t_aufk-aufnr.
endif.
sort t_afko by plnbez.
sort  wt_mara by matnr.
clear:ls_afko,ls_mara.
refresh lt_afko.
loop at t_afko into  ls_afko .
  READ TABLE  wt_mara INTO ls_mara
  with key matnr = ls_afko-plnbez binary search.
  IF sy-subrc EQ 0.
    APPEND ls_afko to lt_afko.
  else.
    clear:ls_afko.
  ENDIF.
endloop.

wt_afko[] = lt_afko[].

IF lines( wt_afko[] ) gt 0.
  SORT t_afko BY aufnr.
ENDIF.

describe table t_afko lines nr_records .
write:nr_records.
GET TIME STAMP FIELD lv_end_time.
lv_diff_f = lv_end_time - lv_sta_time.
WRITE: /(15) 'Select+forall+read', lv_diff_f.
* End   - Processing with Work Area

**********************************************************************
REFRESH:t_afko.
REFRESH:lt_afko.
REFRESH:wt_afko.

CLEAR: lv_flag,
       lv_sta_time,
       lv_end_time.
clear:nr_records.

* Begin - Processing with Field-Symbols
GET TIME STAMP FIELD lv_sta_time.
SELECT A~aufnr   "Order Number
               A~gltrp   "Basic finish date
               A~gamng   "Total order quantity
               A~plnbez  "Material Number
               A~plnty   "Task List Type
               A~plnnr   "Key for Task List Group
               A~plnal   "Group Counter
               FROM afko as A
               JOIN aufk as B ON A~aufnr = B~aufnr
               INTO TABLE t_afko
   WHERE B~bukrs = p_bukrs.

sort t_afko by plnbez.
sort  wt_mara by matnr.
clear:ls_afko,ls_mara.
refresh lt_afko.
loop at t_afko into  ls_afko .
  READ TABLE  wt_mara INTO ls_mara
  with key matnr = ls_afko-plnbez binary search.
  IF sy-subrc EQ 0.
    APPEND ls_afko to lt_afko.
  else.
    clear:ls_afko.
  ENDIF.
endloop.

wt_afko[] = lt_afko[].

IF lines( wt_afko[] ) gt 0.

  SORT t_afko BY aufnr.
ENDIF.

describe table t_afko lines nr_records .
write:nr_records.
GET TIME STAMP FIELD lv_end_time.
lv_diff_f = lv_end_time - lv_sta_time.
WRITE: /(15) 'Inner+read', lv_diff_f.
* End   - Processing with Work Area
**********************************************************************
REFRESH:t_afko.
REFRESH:lt_afko.
REFRESH:wt_afko.
REFRESH:t_aufk.
CLEAR: lv_flag,
       lv_sta_time,
       lv_end_time.
clear:nr_records.

* Begin - Processing with Field-Symbols
GET TIME STAMP FIELD lv_sta_time.

IF wt_mara[] IS NOT INITIAL.
  SELECT A~aufnr   "Order Number
         A~gltrp   "Basic finish date
         A~gamng   "Total order quantity
         A~plnbez  "Material Number
         A~plnty   "Task List Type
         A~plnnr   "Key for Task List Group
         A~plnal   "Group Counter
         FROM afko as A
         JOIN aufk as B ON A~aufnr = B~aufnr
         INTO TABLE t_afko
         WHERE B~bukrs = p_bukrs.
  SORT t_afko ASCENDING BY plnbez.
  LOOP AT wt_mara INTO ls_mara.
    READ TABLE t_afko WITH KEY plnbez = ls_mara-matnr BINARY SEARCH transporting no fields.

    IF sy-subrc EQ 0.
      w_tabix = sy-tabix.
      LOOP AT t_afko INTO ls_AFKO FROM w_tabix.
        IF ls_afko-plnbez NE ls_mara-matnr.
          EXIT.
        ENDIF.
        APPEND ls_afko to lt_afko .              "(LT_AFKO is another table of type Y_AFKO)
      ENDLOOP.
    ENDIF.
  ENDLOOP.
ENDIF.

describe table lt_afko lines nr_records .
write:nr_records.

GET TIME STAMP FIELD lv_end_time.
lv_diff_f = lv_end_time - lv_sta_time.
WRITE: /(15) 'Inner+ParCursor', lv_diff_f.
* End   - Processing with Work Area
**********************************************************************