topic Re: Performace Issue in Application Development and Automation Discussions

Performace Issue

Former Member — Thu, 16 Oct 2008 19:39:17 GMT

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

Re: Performace Issue

former_member156446 — Thu, 16 Oct 2008 19:40:54 GMT

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.

Re: Performace Issue

Former Member — Thu, 16 Oct 2008 19:41:21 GMT

Hi,

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

Re: Performace Issue

Former Member — Thu, 16 Oct 2008 19:42:53 GMT

thanks for the reply,

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

regards,

raghu

Re: Performace Issue

Former Member — Thu, 16 Oct 2008 19:45:55 GMT

Is s_matnr empty??

Rob

Re: Performace Issue

Former Member — Thu, 16 Oct 2008 19:47:47 GMT

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

Re: Performace Issue

karol_seman — Thu, 16 Oct 2008 19:48:23 GMT

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

Re: Performace Issue

former_member156446 — Thu, 16 Oct 2008 19:49:37 GMT

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

Re: Performace Issue

Former Member — Thu, 16 Oct 2008 19:50:15 GMT

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

Re: Performace Issue

Former Member — Thu, 16 Oct 2008 19:52:22 GMT

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

Re: Performace Issue

Former Member — Thu, 16 Oct 2008 19:52:55 GMT

INTO CORRESPONDING FIELDS OF TABLE it_matl

is not a performance problem.

Rob

Re: Performace Issue

Former Member — Thu, 16 Oct 2008 19:55:17 GMT


>   and  the field s_matnr is not initial.

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

Rob

Re: Performace Issue

Former Member — Thu, 16 Oct 2008 20:30:25 GMT

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?

Re: Performace Issue

Former Member — Thu, 16 Oct 2008 20:36:06 GMT

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.

Re: Performace Issue

Former Member — Thu, 16 Oct 2008 20:37:10 GMT

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