urgent -performance

[Former Member]

select a_{mblnr a}mjahr a_{zeile a}matnr a_{lifnr a}ebeln

a_{werks a}menge

b~budat

from mseg as a join mkpf as b on a_{mblnr = b}mblnr

and a_{mjahr = b}mjahr

into corresponding fields of table it_mseg

for all entries in it_vbap

where a~werks = p_werks

and a~bwart = '122'

and b~budat in s_budat

and a~matnr = it_vbap-matnr

and a~werks = it_vbap-werks.

endif.

can any one give alternate for this.........plz

[Former Member]

hi,

IF NOT i_mara[] IS INITIAL.

SELECT mblnr " Material document

mjahr " Material doc. year

zeile " Item in material document

bwart " Movement type

matnr " Material number

werks " Plant

shkzg " Credit/Debit indicator

dmbtr " Amount in loc. currency

menge " Quantity

FROM mseg

INTO TABLE i_mseg

FOR ALL ENTRIES IN i_mara

WHERE matnr EQ i_mara-matnr

AND werks EQ p_werks

AND lgort IN r_lgort

AND bwart IN r_bwart

AND sobkz IN r_sobkz.

This uses secondary index on mseg table......

when u select more records... u program performance goes down....

No alternate for mseg table , I have tried a lot but no answers from any one...

try to improve performance on the other part of ur program...

Regards,

Priya

[Former Member]

hi,

IF NOT i_mara[] IS INITIAL.

SELECT mblnr " Material document

mjahr " Material doc. year

zeile " Item in material document

bwart " Movement type

matnr " Material number

werks " Plant

shkzg " Credit/Debit indicator

dmbtr " Amount in loc. currency

menge " Quantity

FROM mseg

INTO TABLE i_mseg

FOR ALL ENTRIES IN i_mara

WHERE matnr EQ i_mara-matnr

AND werks EQ p_werks

AND lgort IN r_lgort

AND bwart IN r_bwart

AND sobkz IN r_sobkz.

This uses secondary index on mseg table......

when u select more records... u program performance goes down....

No alternate for mseg table , I have tried a lot but no answers from any one...

try to improve performance on the other part of ur program...

Regards,

Priya

[Former Member]

hi,

1. avoid into corresponding

2. split the join. first fetch the data from mseg. then based on the mblnr, mjahr and s_budat fetch the data from mkpf.

3. try to populate a range table for all werks put together.

regards,

madhu

[Former Member]

hi

thanks.. today my first day of work... can u help me to optimize if i send u my program..plz plz...

[Former Member]

Hi meena...

Your total Select Query is incorrect Could You tell Wht You need do actually...

regards,

sg.

[Former Member]

Hi,

You can create a database view with this two tables and than select data from this view.

For my case and with this same tables, worked fine.

Regards,

Fernando

[Former Member]

if not it_vbap[] is initial.

select a_{mblnr a}mjahr a_{zeile a}matnr a_{lifnr a}ebeln

a_{werks a}menge b~budat

into corresponding fields of table it_mseg

from mseg as a

inner join mkpf as b on a_{mblnr = b}mblnr

and a_{mjahr = b}mjahr

for all entries in it_vbap

where a~werks = p_werks

and a~bwart = '122'

and b~budat in s_budat

and a~matnr = it_vbap-matnr

and a~werks = it_vbap-werks.

endif.

This could correct but I suggest You not to Hard Code Movement type and try to declare a type wht Fields You need.

Regards,

sg.

[Former Member]

Hi ..

thanks ..its vendor performance report. it takes 20 hrs to run. i need to optimize...

i tried to run but its not showing any output... how to check..

[Former Member]

Hi Meena,

Is only this Select query You are Using in your whole program, the better way to check is ...try to select the data separetly from one one table using For all entries then you wil come to know whether the data is selecting or not.

Try out..

regards,

sg.

[Former Member]

Hi ..

1. Get the index of each table.

2. Used the field order selection from top to bottom if no table

index created in each table.

3. Select to table consuidering the foreign key first before

secondary.

Try out this...

if not it_vbap[] is initial.

select a_{mblnr a}mjahr a_{zeile a}matnr a_{lifnr a}ebeln a_{werks a}menge b~budat

into table it_mseg

from mseg as a

inner join mkpf as b

on a_{mandt = b}mandt

and a_{mblnr = b}mblnr

and a_{mjahr = b}mjahr

for all entries in it_vbap

where a~werks = p_werks

and a~bwart = '122'

and b~budat in s_budat

and a~matnr = it_vbap-matnr

and a~werks = it_vbap-werks.

endif.

regards,

sg

[Former Member]

Hi Meena,

One quick question, have you limited the data going into the select? I noticed that your for all entries table implies it's from VBAP (it_vbap) which could contain many entries for the same material/plant combinations.

Have you limited this table (or a subset) to contain only a SINGLE entry for each material/plant code?

(Just a thought).

Good luck with your tuning effort.

Andy

[former_member251078]

Hi,

MSEG

-

---

MANDT

MATNR

WERKS

BWART

MKPF

-

---

MANDT

BUDAT

If there is no index exists on the above fields, please create secondary index for the

above tables for improving the performance.

Please create histograms for tables MKPF and MSEG tables as mentioned in SAP Notes 902675 and 921164.

Also please ensure you have the value 5 for the following parameters.

rsdb/max_blocking_factor

rsdb/min_in_blocking_factor

Hope this helps

Regards

Dileep