topic Re: urgent -performance in Application Development and Automation Discussions

urgent -performance

Former Member — Tue, 29 Apr 2008 09:29:44 GMT

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

Re: urgent -performance

Former Member — Tue, 29 Apr 2008 09:36:32 GMT

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

Re: urgent -performance

Former Member — Tue, 29 Apr 2008 09:48:54 GMT

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

Re: urgent -performance

Former Member — Tue, 29 Apr 2008 09:54:47 GMT

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

Re: urgent -performance

Former Member — Tue, 29 Apr 2008 14:44:39 GMT

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

Re: urgent -performance

Former Member — Wed, 30 Apr 2008 02:07:46 GMT

Hi meena...

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

regards,

sg.

Re: urgent -performance

Former Member — Wed, 30 Apr 2008 02:17:00 GMT

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.

Re: urgent -performance

Former Member — Thu, 01 May 2008 13:18:09 GMT

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

Re: urgent -performance

Former Member — Fri, 02 May 2008 02:48:16 GMT

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.

Re: urgent -performance

Former Member — Fri, 02 May 2008 02:59:49 GMT

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

Re: urgent -performance

Former Member — Fri, 02 May 2008 03:31:10 GMT

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,

Re: urgent -performance

former_member251078 — Fri, 02 May 2008 10:05:28 GMT

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