Performace Issue

[Former Member]

hi all,

below code is taking a lot of time in prod server.

can any help me to optimize this code.

SELECT a_{matnr a}ersda

b_{werks b}dispo b_{matnr b}eisbe b~mmsta

c~maktx

INTO CORRESPONDING FIELDS OF TABLE it_matl

FROM ( mara AS a INNER JOIN marc AS b

ON a_{matnr = b}matnr )

INNER JOIN makt AS c

ON b_{matnr = c}matnr

WHERE a~matnr IN s_matnr

AND b~werks IN s_werks

AND mtart EQ 'UNBW'

AND mmsta NE '98'

AND b~dispo IN s_dispo

AND b~kzaus EQ ' '

AND c~spras EQ sy-langu.

regards,

raghu

[former_member156446]

SELECT a~matnr a~ersda
b~werks b~dispo b~matnr b~eisbe b~mmsta
c~maktx
INTO CORRESPONDING FIELDS OF TABLE it_matl "try to remove into corresponding by giving all the field in the same sequence as in structure of it_matl.
FROM ( mara AS a INNER JOIN marc AS b
ON a~matnr = b~matnr )
INNER JOIN makt AS c
ON b~matnr = c~matnr
WHERE a~matnr IN s_matnr
AND b~werks IN s_werks
AND mtart EQ 'UNBW'
AND mmsta NE '98'
AND b~dispo IN s_dispo
AND b~kzaus EQ ' '
AND c~spras EQ sy-langu.

[Former Member]

Hi,

This should goto the Forum for Peformance Tunning......

[Former Member]

thanks for the reply,

but if i remove move corresponding then its giving a dump error.

regards,

raghu

[karol_seman]

why are u selecting matnr twice? If you use matnr for join then you have duplicated value ...

SELECT a_{matnr a}ersda

b_{werks b}dispo b_{matnr b}eisbe b~mmsta

c~maktx

To not have dump you have to have structure of your internal table the same as listed fields in select statement (same order and not more or less than listed...)

Then also check the order of the fields in where condition if are matching order in indeces. If not correct that.

Regards,

Karol

[former_member156446]

its going to dump bcoz ur internal table structure and the sequence of fields in select stmt is not the same.

[Former Member]

Hi,

Before removing the Into Corresponding fields.....you need to change your Internal Table strucutre......you can only have those fields in same order...which u r fetching from SELECT....

[Former Member]

Is s_matnr empty??

Rob

[Former Member]

Hi,

Try this:

INTO CORRESPONDING FIELDS OF TABLE it_matl

Remove Into Corresponding fields of..by chnging the Internal Table Structure....

AND mmsta NE '98'

Replace NE by a suitable condition..

AND b~kzaus EQ ' '

Use keyword SPACE....

Anyway...your Query is having PK MATNR in the where and hence should not take a lot of time......is the number of Materials satisfying this QUERY are a lot in numbers?????

[Former Member]

hi rob and ajay ,

let me create a separate structure and see as you say.

and the field s_matnr is not initial.

regards,

raghu

[Former Member]

>

>   and  the field s_matnr is not initial.

OK - what does it look like? Is a large range being used?

Rob

[Former Member]

INTO CORRESPONDING FIELDS OF TABLE it_matl

is not a performance problem.

Rob

[Former Member]

Are you sure the problem is with the select statement? I've tried it in my system with 40K materials and it takes a couple of seconds. Is the select in a loop perhaps? Or do you have a truly huge number of matnr/werks/dispo?

[Former Member]

first declare it_mat1 as

data: begin of it_mat1,

matnr type....

ersda

werks

dispo

matnr

eisbe

mmsta

maktx

end of it_mat1.

SELECT a_{matnr a}ersda b_{werks b}dispo b_{matnr b}eisbe b_{mmsta c}maktx

INTO TABLE it_matl

FROM ( mara AS a INNER JOIN marc AS b

ON a_{matnr = b}matnr )

INNER JOIN makt AS c

ON a_{matnr = c}matnr

WHERE a~matnr IN s_matnr

AND b~werks IN s_werks

AND mtart EQ 'UNBW'

AND mmsta NE '98'

AND b~dispo IN s_dispo

AND b~kzaus EQ ' '

AND c~spras EQ sy-langu.

[Former Member]

before that have a check that s_matnr is not initial...