topic Re: Better code for fallowing code in Application Development and Automation Discussions

Better code for fallowing code

Former Member — Mon, 14 Jan 2013 05:54:27 GMT

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.

Re: Better code for fallowing code

former_member195270 — Mon, 14 Jan 2013 07:11:22 GMT

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.

Re: Better code for fallowing code

hendrik_brandes — Mon, 14 Jan 2013 07:31:56 GMT

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

Re: Better code for fallowing code

Former Member — Mon, 14 Jan 2013 08:33:21 GMT

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

Re: Better code for fallowing code

former_member491621 — Mon, 14 Jan 2013 08:37:50 GMT

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.

Re: Better code for fallowing code

Former Member — Mon, 14 Jan 2013 08:56:24 GMT

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

Re: Better code for fallowing code

former_member491621 — Mon, 14 Jan 2013 10:14:56 GMT

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

Re: Better code for fallowing code

Former Member — Mon, 14 Jan 2013 12:07:23 GMT

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