Skip to Content
avatar image
Former Member

Performace Issue

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

10 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 16, 2008 at 07: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?????

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 16, 2008 at 07:42 PM

    thanks for the reply,

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

    regards,

    raghu

    Add comment
    10|10000 characters needed characters exceeded

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

  • avatar image
    Former Member
    Oct 16, 2008 at 08: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.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 16, 2008 at 07: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.
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 16, 2008 at 07:41 PM

    Hi,

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 16, 2008 at 07:45 PM

    Is s_matnr empty??

    Rob

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 16, 2008 at 07: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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      >

      > and the field s_matnr is not initial.

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

      Rob

  • avatar image
    Former Member
    Oct 16, 2008 at 07:52 PM
    INTO CORRESPONDING FIELDS OF TABLE it_matl

    is not a performance problem.

    Rob

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 16, 2008 at 08: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?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 16, 2008 at 08:37 PM

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

    Add comment
    10|10000 characters needed characters exceeded