MSEG select performance problem - Please help me (URGERNT)

[Former Member]

I have the following SELECT stmt which is taking more time to execute......Kindly help me how to improve performance ...

YOUR HELP IS HIGHLY APPRECIATED....

SELECT a~mblnr

a~matnr

a~werks

a~charg

a~lifnr

a~bualt

a~erfmg

a~ebeln

a~ebelp

b~budat

INTO CORRESPONDING FIELDS OF TABLE it_mseg_mkpf

FROM mseg AS a INNER JOIN mkpf AS b

ON a_{mblnr EQ b}mblnr

WHERE a~matnr IN s_matnr

AND a~werks IN s_werks

AND a~lifnr IN s_lifnr

AND a~bwart EQ p_bwart

AND ( a_{ebeln NE ' ' AND a}ebeln IN s_ebeln )

AND b~budat IN s_budat.

IF NOT it_mseg_mkpf IS INITIAL.

SELECT matnr bismt zzshelf_life FROM mara INTO TABLE it_mara

* FOR ALL ENTRIES IN it_mseg_mkpf*

* WHERE matnr = it_mseg_mkpf-matnr.*

ENDIF.

IF NOT it_mara IS INITIAL.

SELECT matnr maktx FROM makt INTO TABLE it_makt

* FOR ALL ENTRIES IN it_mara*

* WHERE matnr = it_mara-matnr.* ENDIF.

Thanks in advance,

Regards,

[former_member194613]

Not equal is always a problem on the index, it does tell hat you want, but what you don't,

it can not support an index-search

Here the NE does not help

AND ( a_{ebeln NE ' ' AND a}ebeln IN s_ebeln )

Try two branches for the whole select the one where s_ebeln is NOT initial,

there you write

AND a~ebeln IN s_ebeln

the other branch where s_ebeln is initial, is hopefully less important

AND a~ebeln NE ' '

Siegfried

[raymond_giuseppi]

Join between MSEG and MKPF : Add the MJAHR field in join condition to give full key of table.

ON a_{mblnr EQ b}mblnr AND a_{mjahr EQ b}mjahr

The not-equal test also generates performance problems, try to adjust the s_ebeln to insure that initial value is not selected (if empty use a dummy range like 00000000-999999999)

Regards

[Former Member]

Hi Sam,

Try this.

SELECT a~mblnr

a~matnr

a~werks

a~charg

a~lifnr

a~bualt

a~erfmg

a~ebeln

a~ebelp

b~budat

INTO CORRESPONDING FIELDS OF TABLE it_mseg_mkpf

FROM mkpf AS b INNER JOIN mseg AS a (although result will be same by reversing this join it's better to keep header on left of the join)

ON a_{mblnr EQ b}mblnr and a_{mjahr EQ b}mjahr

WHERE a~matnr IN s_matnr

AND a~werks IN s_werks

AND a~lifnr IN s_lifnr

AND a~bwart EQ p_bwart

AND ( a_{ebeln NE ' ' AND a}ebeln IN s_ebeln )

AND b~budat IN s_budat.

(if possible, give mjahr in you selection screen and specify in where condition)

IF NOT it_mseg_mkpf [ ] IS INITIAL. (previous condition will check header line only)

SELECT matnr bismt zzshelf_life FROM mara INTO TABLE it_mara

FOR ALL ENTRIES IN it_mseg_mkpf

WHERE matnr = it_mseg_mkpf-matnr

ENDIF.

IF NOT it_mara[ ]

IS INITIAL.

SELECT matnr maktx FROM makt INTO TABLE it_makt

FOR ALL ENTRIES IN it_mara

WHERE matnr = it_mara-matnr.

ENDIF.

Regards,

Mohaiyuddin

[Former Member]

Hi Sam,

1) Avoid select into corresponding fields and use select into table.

2) Check the order of fields in the where clause.

With Regards,

Gandhi Subramani