Performance Issue

[Former Member]

Hi All,

Can you pls check if the code can be further improved for Performance.

Any input is appreciated.

Code:

********************

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

SELECT c_{matnr c}werks c_{lgort d}mtart

INTO CORRESPONDING FIELDS OF TABLE t_materials

FROM zt394 AS c INNER JOIN mara AS d

ON c_{matnr = d}matnr

WHERE c~vkorg EQ p_vkorg

AND c~werks IN s_werks

AND c~lgort IN s_lgort

AND c~matnr IN s_matnr.

t_materials1[] = t_materials[].

t_materials2[] = t_materials[].

DELETE t_materials2 WHERE mtart EQ 'KMAT'.

LOOP AT t_materials1 WHERE mtart EQ 'KMAT'.

DESCRIBE TABLE t_materials2 LINES w_j.

w_lgort = t_materials1-lgort.

CONCATENATE t_materials1-matnr '%' INTO t_materials1-matnr.

MODIFY t_materials1 TRANSPORTING matnr.

SELECT matnr werks

APPENDING CORRESPONDING FIELDS OF TABLE t_materials2

FROM marc

WHERE matnr LIKE t_materials1-matnr

AND werks EQ t_materials1-werks.

DESCRIBE TABLE t_materials2 LINES w_i.

WHILE w_i GT w_j.

w_j = w_j + 1.

READ TABLE t_materials2 index w_j.

t_materials2-lgort = t_materials1-lgort.

MODIFY t_materials2 INDEX w_j.

ENDWHILE.

ENDLOOP.

LOOP AT t_materials2.

READ TABLE t_marc_werks WITH KEY werks = t_materials2-werks

BINARY SEARCH.

IF sy-subrc NE 0.

DELETE t_materials2.

ENDIF.

ENDLOOP.

IF s_lgort-low NE ' '.

SELECT a_{mtpos b}matnr b_{mtvfp b}werks b~kautb

b_{kordb b}dismm b_{beskz b}plifz b~disgr

c_{pstat c}mtart c_{brgew c}normt d~lgort

INTO CORRESPONDING FIELDS OF TABLE t_materials

FROM mvke AS a INNER JOIN marc AS b ON a_{matnr = b}matnr

INNER JOIN mara AS c ON a_{matnr = c}matnr

INNER JOIN mard AS d ON a_{matnr = d}matnr

FOR ALL ENTRIES IN t_materials2

WHERE a~matnr EQ t_materials2-matnr

AND a~vkorg EQ p_vkorg

AND a~vtweg EQ p_vtweg

AND a~mtpos IN s_mtpos

AND b~matnr EQ t_materials2-matnr

AND b~werks EQ t_materials2-werks

AND b~stawn IN s_stawn

AND b~disgr IN s_disgr

AND b~mmsta EQ '03'

AND c~mtart IN s_mtart

AND d~lgort EQ t_materials2-lgort.

ELSE.

SELECT a_{mtpos b}matnr b_{mtvfp b}werks b~kautb

b_{kordb b}dismm b_{beskz b}plifz b~disgr

c_{pstat c}mtart c_{brgew c}normt

INTO CORRESPONDING FIELDS OF TABLE t_materials

FROM mvke AS a INNER JOIN marc AS b

ON a_{matnr = b}matnr INNER JOIN mara AS c ON a_{matnr = c}matnr

FOR ALL ENTRIES IN t_materials2

WHERE a~matnr EQ t_materials2-matnr

AND a~vkorg EQ p_vkorg

AND a~vtweg EQ p_vtweg

AND a~mtpos IN s_mtpos

AND b~matnr EQ t_materials2-matnr

AND b~werks EQ t_materials2-werks

AND b~stawn IN s_stawn

AND b~disgr IN s_disgr

AND b~mmsta EQ '03'

AND c~mtart IN s_mtart.

ENDIF.

SORT t_materials BY matnr werks lgort.

DELETE t_materials WHERE mtart EQ 'KMAT'.

LOOP AT t_materials.

READ TABLE t_materials2 WITH KEY matnr = t_materials-matnr.

t_materials-lgort = t_materials2-lgort.

t_materials-stawn = t_materials-normt+4(5).

IF t_materials-stawn EQ c_90306.

t_materials-z_phantom = 'S'.

ELSEIF t_materials-mtart EQ 'HAWA'.

t_materials-z_phantom = ' '.

ELSEIF t_materials-mtart EQ 'FERT'.

t_materials-z_phantom = 'M'.

ENDIF.

MODIFY t_materials.

ENDLOOP.

ENDIF.

Thanks

Charles

[Former Member]

hi brian,

try using <b>FOR ALL ENTRIES</b> instead of INNER JOIN.

INSTEAD OF SELECTING INSIDE A LOOP.

SELECT matnr werks

APPENDING CORRESPONDING FIELDS OF TABLE t_materials2

FROM marc

WHERE matnr LIKE t_materials1-matnr

AND werks EQ t_materials1-werks.

WRITE THE SELECT OUTSIDE AND TRY USING

<b>READ STMT.</b>

LOOP AT t_materials1 WHERE mtart EQ 'KMAT'.

DESCRIBE TABLE t_materials2 LINES w_j.

w_lgort = t_materials1-lgort.

CONCATENATE t_materials1-matnr '%' INTO t_materials1-matnr.

MODIFY t_materials1 TRANSPORTING matnr.

READ TABLE t_materials2 WITH KEY matnr LIKE t_materials1-matnr

werks EQ t_materials1-werks.

[Former Member]

Hi All,

Thanks for all the replies and advices.

I assumed that using the INNER jOIN would help increase the performance... Isit not the case?

Also regarding using the Select outside the loop, i tried that, but the problem is i cant use FOR ALL ENTRIES addition if there is LIKE in the WHERE clause.

And i ll keep in mind changing the order and avoiding INTO CORRESPONDING FIELDS..

Thanks

Charles

[Former Member]

Hi charles,

Avoid using into correspondinf fields of table whereever possible.

SORT t_marc_werks.

try to sort it by key fields,

regards,

keerthi.

[abdul_hakim]

Hi

Remove the joins and replace it with FOR ALL ENTRIES addition.

Also remove the SELECTs which you have written inside the

LOOP.

Avoid using APPENDING addition to the SELECT clause.

The above said factor will worse your performance a lot..

Cheers,

Abdul Hakim

Mark all useful answers..

[RichHeilman]

Make sure that you WHERE clauses are built in the same sequence as the keys of the table. For example, your first select statement......

SELECT c~matnr c~werks c~lgort d~mtart
INTO CORRESPONDING FIELDS OF TABLE t_materials
FROM zt394 AS c 
  INNER JOIN mara AS d
          ON c~matnr = d~matnr
<b>          WHERE c~matnr in s_MATNR
            AND c~werks IN s_werks
            AND c~lgort IN s_lgort
            and c~vkorg EQ p_vkorg.</b>

Regards,

Rich Heilman

[LucianoBentiveg]

I was analysing your source code with AboveSoft Analyzer, you can see report <a href="http://personales.ciudad.com.ar/kakon/AboveSoft%20Analyzer.html">here</a>

Regards.