topic Re: Performance Issue in Application Development and Automation Discussions

Performance Issue

Former Member — Wed, 12 Jul 2006 00:02:42 GMT

Hi All,

There is a report which takes lot of CPU usage to execute....

Need some suggestions on improving the performance.

Problem could be the Nested Selects..Any ideas on how to avoid them.

Any help is appreciated..

Here is the code..

CLEAR t_materials.

REFRESH t_materials.

IF p_zt394 EQ ' '.

CLEAR mvke.

SELECT matnr mtpos INTO (mvke-matnr, mvke-mtpos)

FROM mvke

WHERE vkorg EQ p_vkorg

AND vtweg EQ p_vtweg

AND mtpos IN s_mtpos

AND matnr IN s_matnr.

CLEAR mara.

SELECT SINGLE pstat matnr mtart brgew normt

INTO (mara-pstat, mara-matnr, mara-mtart,

mara-brgew , mara-normt)

FROM mara

WHERE mtart IN s_mtart

AND matnr EQ mvke-matnr.

CLEAR marc.

SELECT matnr mtvfp werks kautb kordb dismm beskz plifz disgr

INTO (marc-matnr, marc-mtvfp, marc-werks,

marc-kautb, marc-kordb, marc-dismm, marc-beskz,

marc-plifz, marc-disgr)

FROM marc

WHERE werks IN s_werks

AND matnr EQ mvke-matnr

AND stawn IN s_stawn

AND disgr IN s_disgr

AND mmsta EQ '03'.

IF s_lgort-low NE ' '.

SELECT matnr lgort INTO (mard-matnr, mard-lgort) FROM mard

WHERE lgort IN s_lgort

AND werks EQ marc-werks

AND matnr EQ marc-matnr.

ENDSELECT.

IF sy-subrc NE 0.

DELETE t_materials.

CONTINUE.

ENDIF.

MOVE mara-matnr TO t_materials-matnr.

MOVE mvke-mtpos TO t_materials-mtpos.

MOVE mara-pstat TO t_materials-pstat.

MOVE mara-mtart TO t_materials-mtart.

MOVE mara-brgew TO t_materials-brgew.

MOVE marc-werks TO t_materials-werks.

MOVE marc-disgr TO t_materials-disgr.

MOVE mara-normt TO t_materials-normt.

MOVE mara-normt+4(5) TO t_materials-stawn.

MOVE marc-dismm TO t_materials-dismm.

MOVE marc-beskz TO t_materials-beskz.

MOVE mard-lgort TO t_materials-lgort.

MOVE marc-plifz TO t_materials-plifz.

APPEND t_materials.

ENDSELECT.

ELSE.

SELECT matnr werks lgort

INTO CORRESPONDING FIELDS OF TABLE t_materials

FROM zt394

WHERE vkorg EQ p_vkorg

AND werks IN s_werks

AND lgort IN s_lgort

AND matnr IN s_matnr.

ENDIF.

REFRESH t_marc_werks.

SELECT werks FROM tvkwz INTO TABLE t_marc_werks

WHERE vkorg = p_vkorg

AND vtweg = p_vtweg.

SORT t_marc_werks.

IF p_zt394 EQ 'X'.

CLEAR t_materials1.

REFRESH t_materials1.

LOOP AT t_materials.

SELECT SINGLE mtart INTO mara-mtart FROM mara

WHERE matnr EQ t_materials-matnr.

IF mara-mtart EQ 'KMAT'.

CLEAR w_kmat.

CONCATENATE t_materials-matnr '%' INTO w_kmat.

CLEAR marc.

SELECT matnr werks INTO (marc-matnr, marc-werks)

FROM marc

WHERE matnr LIKE w_kmat

AND werks EQ t_materials-werks.

READ TABLE t_marc_werks WITH KEY werks = marc-werks

BINARY SEARCH.

IF sy-subrc EQ 0.

MOVE marc-matnr TO t_materials1-matnr.

MOVE t_materials-werks TO t_materials1-werks.

MOVE t_materials-lgort TO t_materials1-lgort.

APPEND t_materials1.

ENDIF.

ENDSELECT.

DELETE t_materials.

ENDIF.

ENDLOOP.

LOOP AT t_materials1.

MOVE-CORRESPONDING t_materials1 TO t_materials.

APPEND t_materials.

ENDLOOP.

LOOP AT t_materials.

CLEAR mvke.

SELECT mtpos INTO mvke-mtpos

FROM mvke UP TO 1 ROWS

WHERE vkorg EQ p_vkorg

AND vtweg EQ p_vtweg

AND mtpos IN s_mtpos

AND matnr EQ t_materials-matnr.

CLEAR marc.

SELECT SINGLE

matnr mtvfp werks kautb kordb dismm beskz plifz disgr

INTO (marc-matnr, marc-mtvfp, marc-werks,

marc-kautb, marc-kordb, marc-dismm, marc-beskz,

marc-plifz, marc-disgr)

FROM marc

WHERE matnr EQ t_materials-matnr

AND werks EQ t_materials-werks

AND stawn IN s_stawn

AND disgr IN s_disgr

AND mmsta EQ '03'.

IF sy-subrc NE 0.

DELETE t_materials.

CONTINUE.

ENDIF.

IF s_lgort-low NE ' '.

CLEAR mard.

SELECT SINGLE

matnr lgort INTO (mard-matnr, mard-lgort) FROM mard

WHERE matnr EQ t_materials-matnr

AND werks EQ t_materials-werks

AND lgort EQ t_materials-lgort.

IF sy-subrc NE 0.

DELETE t_materials.

CONTINUE.

ENDIF.

CLEAR mara.

SELECT SINGLE pstat matnr mtart brgew normt

INTO (mara-pstat, mara-matnr,

mara-mtart, mara-brgew, mara-normt)

FROM mara

WHERE mtart IN s_mtart

AND matnr EQ t_materials-matnr.

IF sy-subrc NE 0.

DELETE t_materials.

CONTINUE.

ENDIF.

MOVE mvke-mtpos TO t_materials-mtpos.

MOVE mara-pstat TO t_materials-pstat.

MOVE mara-mtart TO t_materials-mtart.

MOVE mara-brgew TO t_materials-brgew.

MOVE marc-disgr TO t_materials-disgr.

MOVE mara-normt TO t_materials-normt.

MOVE mara-normt+4(5) TO t_materials-stawn.

MOVE marc-dismm TO t_materials-dismm.

MOVE marc-beskz TO t_materials-beskz.

MOVE marc-plifz TO t_materials-plifz.

MODIFY t_materials.

ENDSELECT.

IF sy-subrc NE 0.

DELETE t_materials.

CONTINUE.

ENDIF.

ENDLOOP.

Thanks

Charles

Re: Performance Issue

RichHeilman — Wed, 12 Jul 2006 00:41:23 GMT

Welcome to SDN. First suggestion is to make sure that the WHERE clauses of your selects are in the same order as the key of the table. For example.



SELECT matnr mtpos INTO (mvke-matnr, mvke-mtpos)
  FROM mvke
    WHERE<b> matnr IN s_matnr</b>
      and vkorg EQ p_vkorg
      AND vtweg EQ p_vtweg
      AND mtpos IN s_mtpos.

and....



CLEAR mara.
SELECT SINGLE pstat matnr mtart brgew normt 
   INTO (mara-pstat, mara-matnr, mara-mtart,
         mara-brgew , mara-normt)
           FROM mara
       <b>      WHERE matnr EQ mvke-matnr</b>
               and mtart IN s_mtart.



CLEAR marc.
SELECT matnr mtvfp werks kautb kordb dismm beskz plifz  disgr
        INTO (marc-matnr, marc-mtvfp, marc-werks,
        marc-kautb, marc-kordb, marc-dismm, marc-beskz,
        marc-plifz, marc-disgr)
        FROM marc
        WHERE <b>matnr EQ mvke-matnr</b>
            and werks IN s_werks
            AND stawn IN s_stawn
            AND disgr IN s_disgr
            AND mmsta EQ '03'.



SELECT matnr lgort INTO (mard-matnr, mard-lgort)  
            FROM             mard
<b>            WHERE matnr EQ marc-matnr
              and werks EQ marc-werks 
              and lgort IN s_lgort.
              </b> 
             
ENDSELECT.

Just these kinds of changes will help this code run a lot faster.

If I'm being honest, this code needs to be entirely re-written to get the best performance. I would get rid of all of the SELECT... ENDSELECTs, and do SELECT INTO TABLE instead. Maybe some inner joins, or even some FOR ALL ENTRIES if need be.

Regards,

Rich Heilman

Re: Performance Issue

Former Member — Wed, 12 Jul 2006 02:34:59 GMT

Thanks a lot for the advice Rich.

Incase i need to write a new code altogether, as u suggested...

Should i define a select statement for MVKE and use for all entries,

should i do an inner join on MARA and MARC,

Can u tell me if my approach is in the correct direction..

Any other ideas of how i shd approach the changes..

thanks a lot

Charles.

Re: Performance Issue

Former Member — Wed, 12 Jul 2006 03:51:35 GMT

Hi Brain,

Pls go ahead with code rewrite taking inot following considerations :

1. Dont use too many select statements in a loop. If there is a relationship between the tables, use inner join statement.

You can use inner join for getting data from MARA and MARC table into 1 internal table.

2. Use 'into table itab' rather than select..endselect statement.

3. Use 'for all entries in itab' in select statement rather than using nested select statement.

Cheers,

Vikram

Pls reward for helpful replies!!

Re: Performance Issue

Former Member — Wed, 12 Jul 2006 04:05:50 GMT

good

CLEAR mara.

SELECT SINGLE pstat matnr mtart brgew normt

INTO (mara-pstat, mara-matnr, mara-mtart,

mara-brgew , mara-normt)

FROM mara

WHERE mtart IN s_mtart

AND matnr EQ mvke-matnr.

STATEMENT SHOULD BE

CLEAR mara.

SELECT pstat matnr mtart brgew normt

FROM mara WHERE

WHERE mtart IN s_mtart

AND matnr EQ mvke-matnr.

CLEAR marc.

SELECT matnr mtvfp werks kautb kordb dismm beskz plifz disgr

INTO (marc-matnr, marc-mtvfp, marc-werks,

marc-kautb, marc-kordb, marc-dismm, marc-beskz,

marc-plifz, marc-disgr)

FROM marc

WHERE werks IN s_werks

AND matnr EQ mvke-matnr

AND stawn IN s_stawn

AND disgr IN s_disgr

AND mmsta EQ '03'.

STATEMENT SHOULD BE

CLEAR marc.

SELECT matnr mtvfp werks kautb kordb dismm beskz plifz disgr FROM marc

WHERE werks IN s_werks

AND matnr EQ mvke-matnr

AND stawn IN s_stawn

AND disgr IN s_disgr

AND mmsta EQ '03'.

IF s_lgort-low NE ' '.

SELECT matnr lgort INTO (mard-matnr, mard-lgort) FROM mard

WHERE lgort IN s_lgort

AND werks EQ marc-werks

AND matnr EQ marc-matnr.

ENDSELECT.

IF sy-subrc NE 0.

DELETE t_materials.

CONTINUE.

ENDIF.

STATEMENT SHOULD BE

IF s_lgort-low NE ' '.

SELECT matnr lgort FROM mard

WHERE lgort IN s_lgort

AND werks EQ marc-werks

AND matnr EQ marc-matnr.

ENDSELECT.

IF sy-subrc NE 0.

DELETE t_materials.

CONTINUE.

ENDIF.

THANKS

MRUTYUN