topic Re: perfomance tuning in Application Development and Automation Discussions

perfomance tuning

Former Member — Tue, 26 Jun 2007 14:48:48 GMT

hi,

can any one can give alternative code for select statement in the code, to increase the perforamnce i have

to change it.

plz forward the code.

SELECT mara_{matnr marc}werks mara_{mtart mara}matkl mara~meins

marc_{dismm marc}dispo marc_{disgr marc}mmsta marc~perkz

marc~periv

FROM mara

INNER JOIN marc

ON mara_{matnr = marc}matnr

INTO CORRESPONDING FIELDS OF TABLE it_alv

WHERE mara~matnr IN so_matnr

AND mara~matkl IN so_matkl

AND mara~mtart IN so_mtart

AND marc~werks IN so_werks

AND marc~disgr IN so_disgr

AND marc~dismm IN so_dismm

AND marc~dispo IN so_dispo.

DESCRIBE TABLE it_alv LINES w_nbitems.

l_count = 0.

LOOP AT it_alv INTO is_alv.

l_tabix = sy-tabix.

l_count = l_count + 1.

PERFORM sr_progress_bar USING l_count

w_nbitems

text-m02.

Read last stock movement for selected material

SELECT mkpf_{budat mkpf}mblnr mkpf_{mjahr mseg}zeile mseg~bwart

mseg_{menge mseg}kostl mkpf_{usnam mseg}wempf

UP TO 1 ROWS

FROM mseg

JOIN mkpf

ON mkpf_{mblnr = mseg}mblnr

AND mkpf_{mjahr = mseg}mjahr

INTO (is_alv-budat, is_alv-mblnr, is_alv-mjahr, is_alv-zeile,

is_alv-bwart, is_alv-menge, is_alv-kostl, is_alv-usnam,

is_alv-wempf)

WHERE mseg~matnr = is_alv-matnr

AND mseg~werks = is_alv-werks

AND mseg~bwart IN so_bwart

ORDER BY MKPF~BUDAT descending.

ENDSELECT.

IF is_alv-budat >= w_startdate. " OR is_alv-budat IS INITIAL.

DELETE it_alv INDEX l_tabix.

CONTINUE.

ENDIF.

*Message was edited by:

Mohammed Toufeeq

Message was edited by:

Mohammed Toufeeq

Re: perfomance tuning

Former Member — Tue, 26 Jun 2007 14:54:56 GMT

join is not advisable to use. It will affect ur performance.

Instead of this u can use For all entries.

to do this for the below scenario.

Select fields from table1 and put it into 1st internal table.

Select fields from table 2 for all entries in internal table 1 where matnr = itab-matnr....

before for all entries u should

Sort the internal table and delete duplicate entries.

need ur reward points if its useful.

Regards

ravi

Re: perfomance tuning

Former Member — Tue, 26 Jun 2007 16:02:19 GMT

I responded on your other post.

Rather than makingthe same post more than once, you can simply edit the first one and it will go to the top of the list.

Rob

Re: perfomance tuning

Former Member — Thu, 28 Jun 2007 06:16:38 GMT

Hi,

I want to suggest few points based on your code

a). Dont use joins instead use for all entries.

b). While passing values into internal table from select query..dont use into corresponding fields. which hits database server very badly.instead create internal table with the structure u want.

c). While using select single make sure that the fields that u r using in the where condition should be key fields

d).Better not to use select * , take the required fields in the select query accordingly create the Internal table strucutre

Thanks,

Phani.

Re: perfomance tuning

Former Member — Thu, 28 Jun 2007 12:11:56 GMT

hi,

three big problem in ur query:

1. do not use non KEY fields inb where clause of select statemt

2. Do not use select ... endselect. instead of this use for all entries and never use select inside loop.

3. remove perform progressbar.

others:

1. remvoe move corrospondig

2. do not use order by in SQL

Jogdand M B