10-16-2008 8:39 PM
hi all,
below code is taking a lot of time in prod server.
can any help me to optimize this code.
SELECT amatnr aersda
bwerks bdispo bmatnr beisbe b~mmsta
c~maktx
INTO CORRESPONDING FIELDS OF TABLE it_matl
FROM ( mara AS a INNER JOIN marc AS b
ON amatnr = bmatnr )
INNER JOIN makt AS c
ON bmatnr = cmatnr
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
10-16-2008 8:47 PM
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?????
10-16-2008 8:40 PM
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.
10-16-2008 8:41 PM
10-16-2008 8:42 PM
thanks for the reply,
but if i remove move corresponding then its giving a dump error.
regards,
raghu
10-16-2008 8:48 PM
why are u selecting matnr twice? If you use matnr for join then you have duplicated value ...
SELECT amatnr aersda
bwerks bdispo bmatnr beisbe 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
10-16-2008 8:49 PM
its going to dump bcoz ur internal table structure and the sequence of fields in select stmt is not the same.
10-16-2008 8:50 PM
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....
10-16-2008 8:45 PM
10-16-2008 8:47 PM
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?????
10-16-2008 8:52 PM
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
10-16-2008 8:55 PM
>
> and the field s_matnr is not initial.
OK - what does it look like? Is a large range being used?
Rob
10-16-2008 8:52 PM
INTO CORRESPONDING FIELDS OF TABLE it_matl
is not a performance problem.
Rob
10-16-2008 9:30 PM
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?
10-16-2008 9:36 PM
first declare it_mat1 as
data: begin of it_mat1,
matnr type....
ersda
werks
dispo
matnr
eisbe
mmsta
maktx
end of it_mat1.
SELECT amatnr aersda bwerks bdispo bmatnr beisbe bmmsta cmaktx
INTO TABLE it_matl
FROM ( mara AS a INNER JOIN marc AS b
ON amatnr = bmatnr )
INNER JOIN makt AS c
ON amatnr = cmatnr
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.
10-16-2008 9:37 PM