How to improve performance while using MSEG ?

[Former Member]

Dear Experts,

Requirement :

Baseline date in MIRO screen will pick up automatically based on respective PURCHASE ORDER GRN  Posting Date. If MIRO contains multiple PO & GRN, system will pick up the 1st line item PO andits respective 1st GRN posting date as BASE LINE date in MIRO.

If there is no GRN postings for 101 Movement for PO’s in MIRO then BASE LINE date will be posting date of MIRO.

BADI Implemented:

MRM_PAYMENT_TERMS

Method PAYMENT_TERMS_SET

Code:

method if_ex_mrm_payment_terms~payment_terms_set.

types : begin of ty_mseg,     

    mblnr type mseg-mblnr,

    mjahr type mseg-mjahr,

    zeile type mseg-zeile,

    end of ty_mseg.

  types : begin of ty_mseg_service,

    mblnr type mseg-mblnr,

    mjahr type mseg-mjahr,

    zeile type mseg-zeile,

    lfbnr type mseg-lfbnr,

    lfpos type mseg-lfpos,

    end of ty_mseg_service.

  types : begin of ty_essr,      

    lblni type essr-lblni, 

    budat type essr-budat, 

    end of ty_essr.

  data : it_drseg   type mmcr_tdrseg,                   

    wa_drseg   type line of mmcr_tdrseg,           

    it_mseg    type table of ty_mseg,              

    wa_mseg    type ty_mseg,                       

    it_mseg_s  type table of ty_mseg_service,      

    wa_mseg_s  type ty_mseg_service,               

    wa_essr    type ty_essr.                       

  it_drseg = ti_drseg.

  select mblnr

    mjahr

    zeile

    from mseg into table it_mseg

    for all entries in it_drseg

    where mblnr = it_drseg-mblnr

    and   mjahr = it_drseg-mjahr

    and   bwart = '101'.

  if it_mseg is not initial.

    loop at it_drseg into wa_drseg.

    read table it_mseg into wa_mseg with key mblnr = wa_drseg-mblnr  mjahr = wa_drseg-mjahr.

    if sy-subrc ne 0.

    delete it_drseg.

    endif.

    endloop.

    clear wa_drseg.

    read table it_drseg into wa_drseg index 1.

    if it_drseg is not initial.

    e_zfbdt = wa_drseg-webud.

    else.

    e_zfbdt = i_rbkpv-zfbdt.

    endif.

  endif.

  select mblnr

    mjahr

    zeile

    lfbnr

    lfpos

    from mseg into table it_mseg_s

    for all entries in it_drseg

    where ebeln = it_drseg-ebeln

    and   ebelp = it_drseg-ebelp

    and   lfbnr = it_drseg-lfbnr

    and   bwart = '101'.

  if it_mseg_s is not initial.

    clear : wa_drseg, wa_mseg_s.

    loop at it_drseg into wa_drseg.

    read table it_mseg_s into wa_mseg_s with key lfbnr = wa_drseg-lfbnr.

    if sy-subrc ne 0.

    delete it_drseg.

    endif.

    endloop.

    clear : wa_drseg, wa_mseg_s.

    read table it_drseg into wa_drseg index 1.

    select single lblni

    budat

    from essr into wa_essr

    where lblni = wa_drseg-lfbnr.

    if wa_essr-budat is not initial.

    e_zfbdt = wa_essr-budat.

    else.

    e_zfbdt = i_rbkpv-zfbdt.

    endif.

  endif.

  if e_zfbdt is initial.

    e_zfbdt = i_rbkpv-zfbdt.

  endif.

  e_zbd1t = i_rbkpv-zbd1t.

  e_zbd1p = i_rbkpv-zbd1p.

  e_zbd2t = i_rbkpv-zbd2t.

  e_zbd2p = i_rbkpv-zbd2p.

  e_zbd3t = i_rbkpv-zbd3t.

  e_zlspr = i_rbkpv-zlspr.

endmethod.

Performance very bad... how to improve performance....

Thanks in advance...

[karun_prabhu]

Hello Ganesh.

     SORT the internal table(it_mseg & it_mseg_s) by key field(s) and READ it with the addition BINARY SEARCH.

     Regarding (2nd select) data fetch from MSEG table, if the fields you are passing are secondary indexes? If not, think of creating EBELN EBELP and BWART as secondary indexes.

Regards.

[former_member188251]

Hi Former Member

-->Check if the sequence of fields in the SELECT and the WHERE statements are as per the table SEQUENCE

--> Remove the delete it_drseg inside the loop and try deleting outside the loop using a single delete statement.

--> Remove the single single and replace with a READ. Select all the records once and READ it.

--> Add binary search to your reads (after SORT)

--> Check if it_drseg is NOT INITIAL  before the 2nd select from MSEG.

If these don't help, do an SQL trace of the MSEG selects and decide whether to go for an index or not.

BR,

Shankar

[Former Member]

Hi ,

You can check with the field symbols . This will improve the performance.

Regards,

Raghu

[Former Member]

If it_drseg [ ] is not initial. 

select mblnr

    mjahr

    zeile

    lfbnr

    lfpos

    from mseg into table it_mseg_s

    for all entries in it_drseg

    where ebeln = it_drseg-ebeln

    and   ebelp = it_drseg-ebelp

    and   lfbnr = it_drseg-lfbnr

    and   bwart = '101'.

endif.

problem araised because you are selecting data using non Primary keys i.e,

EBELN , EBELP,LFNBR .

Please try change the table link .

[Former Member]

HI,

before executing the select on mseg you should check it_drseg is not initial as for all entries would otherwise select complete mseg.

Mseg is a huge and fast growing table so please check the fields in your where condition. At least in our system i can not find these fields as index fields. Think about creating a new index.

BR

Robert

[miko2]

Did you try to make an index with this fields, but you must be careful if you do this for the storage allocation the new index will demand in the system.

[Former Member]

as the other members mentioned the performance is bad mainly because of MSEG non-key selection from the database , check if this is mandatory for business as not to use the primary keys, if so then there is no other go than creating secondary index on the fields you are using for the selection from database MSEG .

  in your case EBELN , EBELP,LFNBR need to be considered for secondary index.

Regards

Venkat.

[Former Member]

Hi,

Create a secondary index on mseg.

regards

Ajit