Skip to Content
avatar image
Former Member

Performance Issues

Hi all,

I am posting my sample code for subroutine. Can anyone please suggest me some means to improve the performance of this code. This program takes almost 17 hours in our DR and PR. So using se30 wont help me to analyze it immediately . Also I know I need to eliminate the nested selects and select * statement. Can anyone suggest me any other tips to improve the performance of this program. Will secondary indexes help me in this regard ?

Code sample :

*********************************************************

FORM get_data_no_sobsk.

  • Extract the material by plant, then corresponding details-----

SELECT *

FROM marc

WHERE werks IN s_werks.

  • and sobsk ne space.

  • check marc-sobsk ca 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.

  • Material Descriptions

CLEAR makt.

SELECT *

FROM makt

WHERE matnr = marc-matnr

AND spras IN s_spras.

  • General Material Data

CLEAR mara.

SELECT *

FROM mara

WHERE matnr = marc-matnr

AND mtart IN s_mtart.

  • Plant description for destination company

CLEAR t001w-name1.

SELECT name1

UP TO 1 ROWS

INTO t_material-dstcomp

FROM t001w

WHERE werks = marc-werks.

ENDSELECT.

  • Material Valuation

CLEAR mbew.

SELECT *

UP TO 1 ROWS

FROM mbew

WHERE matnr = marc-matnr

AND bwkey = marc-werks.

ENDSELECT.

  • Material Valuation

CLEAR t460t-ltext.

SELECT SINGLE ltext

INTO t_material-ltext

FROM t460t

WHERE werks = marc-werks

AND sobsl = marc-sobsk

AND spras IN s_spras.

  • format internal table with fields ready for unix

MOVE-CORRESPONDING mara TO t_material.

MOVE-CORRESPONDING marc TO t_material.

MOVE-CORRESPONDING mbew TO t_material.

MOVE-CORRESPONDING makt TO t_material.

t_material-cr = w_eor.

  • insert cariage return and tabs

t_material-cr = w_eor.

t_material-tab1 = w_tab.

t_material-tab2 = w_tab.

t_material-tab3 = w_tab.

t_material-tab4 = w_tab.

t_material-tab5 = w_tab.

t_material-tab6 = w_tab.

t_material-tab7 = w_tab.

t_material-tab8 = w_tab.

t_material-tab9 = w_tab.

t_material-tab10 = w_tab.

t_material-tab11 = w_tab.

t_material-tab12 = w_tab.

t_material-tab13 = w_tab.

t_material-tab14 = w_tab.

t_material-tab15 = w_tab.

t_material-tab16 = w_tab.

t_material-tab17 = w_tab.

t_material-tab18 = w_tab.

t_material-tab19 = w_tab.

t_material-tab20 = w_tab. " PRS #1386042

t_material-tab21 = w_tab. "TAG 5013

WRITE mbew-bwprh TO t_material-bwprh NO-GROUPING.

  • WRITE mbew-stprs TO t_material-stprs NO-GROUPING.

  • Create the internal table with extracted data

APPEND t_material.

ENDSELECT.

ENDSELECT.

ENDSELECT.

ENDFORM. " get_Data_no_sobsk

*********************************************************

<b>Note : I am not the edeveloper of this code.</b>

Regards,

Ben.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

8 Answers

  • Best Answer
    avatar image
    Former Member
    May 09, 2006 at 02:17 PM

    *First n foremost, try to avoid using select * . give the names of the fields instead in the select statement.

    *Instead of select/endselect, use select into table..

    Regards..

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 09, 2006 at 02:13 PM

    Hi Daniel,

    Use <b>SELECT <feild1> <feild2> .....<feildn> from <dtab></b> instead of <b>SELECT * from <dtab></b>.

    Thank you,

    Ramu N.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 09, 2006 at 02:15 PM

    Hai Daniel

    use for all Entries for improving Performence and also Not use Select--Endselect instead. Better to use select .... from Tablename into table Internal table

    I will send a sample report

    Check it

    REPORT zs_stockreport3

    NO STANDARD PAGE HEADING

    LINE-SIZE 255

    MESSAGE-ID zz1.

    ************************************************************************

    • Table Declaration *

    ************************************************************************

    TABLES: mara,

    marc,

    mard.

    ************************************************************************

    • Types Declaration *

    ************************************************************************

    TYPES: BEGIN OF typ_mara,

    matnr TYPE mara-matnr, "Material Number"

    mbrsh TYPE mara-mbrsh, "Industrial Sector"

    mtart TYPE mara-mtart, "Material Type"

    meins TYPE mara-meins, "Base Unit of Measure"

    END OF typ_mara.

    TYPES: BEGIN OF typ_makt,

    matnr TYPE makt-matnr, "Material Number"

    maktx TYPE makt-maktx, "Material Description"

    END OF typ_makt.

    TYPES: BEGIN OF typ_marc,

    matnr TYPE marc-matnr, "Material Number"

    werks TYPE marc-werks, "Plant Number"

    END OF typ_marc.

    TYPES: BEGIN OF typ_mard,

    matnr TYPE marc-matnr, "Material Number"

    werks TYPE marc-werks, "Plant Number"

    lgort TYPE mard-lgort, "Storage Location"

    END OF typ_mard.

    ************************************************************************

    • Intrnal tables Declaration *

    ************************************************************************

    DATA: it_mara TYPE STANDARD TABLE OF typ_mara WITH HEADER LINE.

    DATA: it_makt TYPE STANDARD TABLE OF typ_makt WITH HEADER LINE.

    DATA: it_marc TYPE STANDARD TABLE OF typ_marc WITH HEADER LINE.

    DATA: it_mard TYPE STANDARD TABLE OF typ_mard WITH HEADER LINE.

    ************************************************************************

    • Variable Declaration *

    ************************************************************************

    DATA: v_count TYPE i.

    ************************************************************************

    • Selection Screen *

    ************************************************************************

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

    SELECT-OPTIONS : s_matnr FOR mara-matnr.

    SELECTION-SCREEN : SKIP.

    PARAMETERS : p_mtart LIKE mara-mtart.

    SELECTION-SCREEN : END OF BLOCK b1.

    ************************************************************************

    • Start Events *

    ************************************************************************

    ************************************************************************

    • Initialization *

    ************************************************************************

    INITIALIZATION.

    ***

    PERFORM initial_input.

    ************************************************************************

    • At Selection-screen *

    ************************************************************************

    AT SELECTION-SCREEN.

    ***

    PERFORM validte_inputdata.

    ************************************************************************

    • Start of Selection *

    ************************************************************************

    START-OF-SELECTION.

    ***

    SELECT matnr

    mbrsh

    mtart

    meins

    INTO TABLE it_mara

    FROM mara

    WHERE matnr IN s_matnr and

    mtart = p_mtart.

    if sy-subrc = 0.

    sort it_mara by matnr.

    else.

    MESSAGE e001 WITH 'No data Found' ' For the Given'

    'Selection Criteria'(400).

    endif.

    IF NOT it_mara[] IS INITIAL.

    SELECT matnr

    maktx

    INTO TABLE it_makt FROM makt

    FOR ALL ENTRIES IN it_mara

    WHERE matnr = it_mara-matnr

    AND spras = sy-langu.

    if sy-subrc = 0.

    sort it_makt by matnr.

    endif.

    ENDIF.

    IF NOT it_mara[] IS INITIAL.

    select matnr

    werks

    from marc

    into table it_marc

    for all entries in it_mara

    where matnr = it_mara-matnr.

    if sy-subrc = 0.

    sort it_marc by matnr werks.

    endif.

    endif.

    IF NOT it_marc[] IS INITIAL.

    select matnr

    werks

    lgort

    from mard

    into table it_mard

    for all entries in it_marc

    where matnr = it_marc-matnr and

    werks = it_marc-werks.

    if sy-subrc = 0.

    sort it_mard by matnr werks lgort.

    endif.

    endif.

    DATA: a TYPE i.

    loop at it_mara.

    a = sy-tabix MOD 2.

    IF a = 1.

    FORMAT COLOR 5.

    ELSE.

    FORMAT COLOR OFF.

    ENDIF.

    read table it_marc with key matnr = it_mara-matnr

    binary search.

    if sy-subrc = 0.

    read table it_mard with key matnr = it_marc-matnr

    werks = it_marc-werks

    binary search.

    if sy-subrc = 0.

    read table it_makt with key matnr = it_mara-matnr

    binary search.

    if sy-subrc = 0.

    WRITE:/ sy-vline.

    WRITE: 2 it_mara-matnr,

    18 sy-vline, it_mara-mbrsh,

    38 sy-vline, it_mara-mtart,

    54 sy-vline, it_mara-meins,

    78 sy-vline, it_makt-maktx,

    120 sy-vline, it_marc-werks,

    135 sy-vline, it_mard-lgort, 155 sy-vline.

    clear : it_mara.

    endif.

    clear : it_makt.

    endif.

    endif.

    endloop.

    WRITE: /(155) sy-uline.

    ************************************************************************

    • Top of Page *

    ************************************************************************

    TOP-OF-PAGE.

    ***

    FORMAT COLOR 1.

    WRITE: /(155) sy-uline.

    WRITE:/ sy-vline.

    WRITE: 2 'Material Number',

    18 sy-vline, 'Industrial Sector',

    38 sy-vline, 'Material Type',

    54 sy-vline, 'Base Unit of Measure',

    78 sy-vline, 'Material Description',

    120 sy-vline, 'Plant',

    135 sy-vline, 'Storage Location',155 sy-vline.

    WRITE: /(155) sy-uline.

    FORMAT RESET.

    ************************************************************************

    • End of Page *

    ************************************************************************

    &----


    *& Form Initial_Input

    &----


    • Initailization of Select Option

    ----


    FORM initial_input .

    CLEAR s_matnr.

    REFRESH s_matnr.

    s_matnr-low = '100-100'.

    s_matnr-high = '111-111'.

    s_matnr-sign = 'I'.

    s_matnr-option = 'BT'.

    APPEND s_matnr.

    p_mtart = 'ROH'.

    ENDFORM. " Initial_Input

    &----


    *& Form Validte_Inputdta

    &----


    • Validation of Select Option

    ----


    FORM validte_inputdata .

    SELECT SINGLE * FROM mara

    WHERE matnr IN s_matnr.

    IF sy-subrc <> 0.

    MESSAGE e001 WITH 'Material'(002) 'Type'(003) 'Does Not Exit'(400).

    ENDIF.

    ENDFORM. " Validte_Inputdta

    ************************************************************************

    • End of Selection *

    ************************************************************************

    Thanks & regards

    Sreenivasulu P

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 09, 2006 at 02:19 PM

    Hi Daniel,

    Your main problem is the SELECT and ENDSELECT commands. Remove SELECT and ENDSELECT and use an array fetch which will bring data at one shot.

    The other main problem is your initial select is on MARC table. This will get duplicate entries of material in the internal table for which you are further using in your select statement. This means for the same material number the Select statement will be triggered twice.

    I can see a lot more options where it can definitely be improved. This is just a dead code as per me. There is a lot of scope for improvement.

    Cheers

    VJ

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 09, 2006 at 02:20 PM

    HI

    GOOD

    I AM MENTIONING SOME OF THE POINTS HERE JUST USE THEM IN YOUR CODE.

    1- DONT USE SELECT * IF YOU R ACCESSING SINGLE FIELD FORM THE DATABASE TABLE.

    2-TRY TO USE CORRESPONDIG FIELDS OF STATEMENT IN YOUR SQL QUERY STATEMENT.

    3-USE THE TCODE SCI(CODE INSPECTOR) TO FURTHER ANALYSE YOUR CODE.

    THANKS

    MRUTYUN

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Mr.Tripathy,

      Can you please explain me in detail how to use code inspector to analyse the code.

      Regards,

      Ben.

      Message was edited by: Daniel Ben

  • May 09, 2006 at 02:21 PM

    I will suggest you to use

    select into table instead of select endselect.

    and also use for all entries if required.

    select endselect causing the performance.

    Regards

    vijay

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 09, 2006 at 02:59 PM

    Hi,

    • Do not use select-exit-endselect logic. Use select single or select up to 1 rows. The trace shows this is more efficient.

    • Avoid using select-endselect loop with logic in the middle. This keeps unnecessary communication to DB2 open. Instead, select the data you need into an internal table and loop at the internal table for your looping logic.

    • Order your WHERE clause exactly to the primary key or index,

    • Avoid using select * and Select individual fields instead where practical.

    • In order to use the addition ‘FOR ALL ENTRIES IN <TABLE>’, make sure the table is not empty or it will select all rows.

    • Avoid using the addition ‘INTO CORRESPONDING FIELDS OF TABLE <TABLE>’, we have seen that it is very CPU intensive. Order the fields in the internal table and the select statement the same and use ‘INTO TABLE <TABLE>’.

    • Be careful with the ‘OR’ and ‘IN” operators in the where clause of an SQL statement. Their use can exclude the use of existing secondary indexes

    • Always use SQL trace (ST05) to examine the SQL statements in your code to determine whether statements are scanning tables or using the index of a table.

    • Always use runtime analysis (SE30) to find out how well a program is running and where it is spending most of its time.

    hope this would be usefull..

    If u need any further clarifications please get back

    Thanks

    Jhansi

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 09, 2006 at 02:31 PM

    Ben,

    1.Remove all nested Select statments.

    2. Do not use " Select * "...alway specify field names eg. "Select MATNR ....."

    3. Rather than selecting data separately from MARA, MARC & MAKT use joins which will be much faster.

    4. Try to use key fields in "Where clause"

    5. Select all data from MBEW into internal table for all entries in first internal table ( combination of MARA, MAKT, MARC) and then process data from internal table.

    Cheers,

    Nilesh

    Message was edited by: Nilesh Kshirsagar

    Message was edited by: Nilesh Kshirsagar

    Add comment
    10|10000 characters needed characters exceeded