topic Re: How to improve performance while using MSEG ? in Application Development and Automation Discussions

How to improve performance while using MSEG ?

Former Member — Fri, 11 Oct 2013 02:51:49 GMT

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.

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.

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

Re: How to improve performance while using MSEG ?

Arun_Prabhu_K — Fri, 11 Oct 2013 03:16:30 GMT

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.

Re: How to improve performance while using MSEG ?

former_member188251 — Fri, 11 Oct 2013 03:38:27 GMT

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

Re: How to improve performance while using MSEG ?

Former Member — Fri, 11 Oct 2013 04:42:01 GMT

Hi ,

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

Regards,

Raghu

Re: How to improve performance while using MSEG ?

Former Member — Fri, 11 Oct 2013 05:21:45 GMT

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 .

Re: How to improve performance while using MSEG ?

Former Member — Fri, 11 Oct 2013 05:25:22 GMT

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.

Robert

Re: How to improve performance while using MSEG ?

miko2 — Fri, 11 Oct 2013 09:01:25 GMT

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.

Re: How to improve performance while using MSEG ?

Former Member — Fri, 11 Oct 2013 09:34:16 GMT

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.

Re: How to improve performance while using MSEG ?

Former Member — Fri, 11 Oct 2013 11:07:36 GMT

Hi,

Create a secondary index on mseg.

regards

Ajit