topic Re: Performance tune in Application Development and Automation Discussions

Performance tune

Former Member — Tue, 01 May 2007 16:01:25 GMT

dear Experts,

I have a performance issue in the below program, the database usage is 94.5 percent, I find difficult to optimize , will any one give me the suggestion where i neeed to change the code.

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

REPORT zmatextc NO STANDARD PAGE HEADING

MESSAGE-ID zf

LINE-SIZE 500.

TYPE-POOLS:slis.

TABLES: mara,

mard,

marc,

makt,

mbew,

t001w.

DATA: BEGIN OF it_out OCCURS 10,

matnr TYPE mara-matnr,

maktx TYPE makt-maktx,

brgew TYPE mara-brgew,

gewei TYPE mara-gewei,

stawn TYPE marc-stawn,

werks TYPE mard-werks,

herkl TYPE marc-herkl,

stprs TYPE mbew-stprs,

spras TYPE makt-spras,

mod(1) TYPE c VALUE 'A',

END OF it_out.

DATA: BEGIN OF it_out1 OCCURS 0.

INCLUDE STRUCTURE zmec.

DATA: END OF it_out1.

DATA: BEGIN OF it_outc OCCURS 0.

INCLUDE STRUCTURE zmec1.

DATA: END OF it_outc.

DATA: BEGIN OF it_out2 OCCURS 10,

matnr TYPE mara-matnr,

fname TYPE cdpos-fname,

value TYPE cdpos-value_new,

tabkey TYPE cdpos-tabkey,

udate TYPE cdhdr-udate,

mod(1) TYPE c VALUE 'C',

END OF it_out2.

DATA: BEGIN OF it_outm OCCURS 10,

matnr TYPE mara-matnr,

maktx TYPE makt-maktx,

brgew TYPE zmec-brgew,

gewei TYPE mara-gewei,

stawn TYPE marc-stawn,

herkl TYPE marc-herkl,

stprs TYPE zmec-stprs,

mod(1) TYPE c VALUE 'C',

END OF it_outm.

DATA: BEGIN OF it_final OCCURS 10,

matnr TYPE mara-matnr,

maktx TYPE makt-maktx,

brgew TYPE zmec-brgew,

gewei TYPE mara-gewei,

stawn TYPE marc-stawn,

herkl TYPE marc-herkl,

stprs TYPE zmec-stprs,

mod(1) TYPE c ,

END OF it_final.

DATA:BEGIN OF it_out3 OCCURS 0,

matnr TYPE mara-matnr,

fname TYPE cdpos-fname,

value TYPE cdpos-value_new,

udate TYPE cdhdr-udate,

utime TYPE cdhdr-utime,

END OF it_out3.

DATA:BEGIN OF it_cdhdr OCCURS 0,

objectclas TYPE cdhdr-objectclas,

objectid TYPE cdhdr-objectid,

changenr TYPE cdhdr-changenr,

udate TYPE cdhdr-udate,

utime TYPE cdhdr-utime,

tcode TYPE cdhdr-tcode,

matnr TYPE matnr,

END OF it_cdhdr.

DATA:BEGIN OF it_cdhdr1 OCCURS 0,

objectclas TYPE cdhdr-objectclas,

objectid TYPE cdhdr-objectid,

changenr TYPE cdhdr-changenr,

udate TYPE cdhdr-udate,

utime TYPE cdhdr-utime,

tcode TYPE cdhdr-tcode,

END OF it_cdhdr1.

DATA:BEGIN OF it_cdpos OCCURS 0,

objectclas TYPE cdpos-objectclas,

objectid TYPE cdpos-objectid,

changenr TYPE cdpos-changenr,

tabname TYPE cdpos-tabname,

tabkey TYPE cdpos-tabkey,

fname TYPE cdpos-fname,

chngind TYPE cdpos-chngind,

value_new TYPE cdpos-value_new,

END OF it_cdpos.

DATA:BEGIN OF it_chg OCCURS 0,

udate LIKE cdhdr-udate,

tcode LIKE cdhdr-tcode,

tabname LIKE cdpos-tabname,

fname LIKE cdpos-fname,

value_new LIKE cdpos-value_new,

END OF it_chg.

DATA:BEGIN OF it_change OCCURS 0,

objectclas LIKE cdhdr-objectclas,

objectid LIKE cdhdr-objectid,

changenr LIKE cdhdr-changenr,

werks LIKE marc-werks,

END OF it_change.

DATA:BEGIN OF it_mara OCCURS 0,

matnr TYPE mara-matnr,

brgew TYPE mara-brgew,

gewei TYPE mara-gewei,

END OF it_mara.

DATA: BEGIN OF it_makt OCCURS 0,

matnr TYPE makt-matnr,

spras TYPE makt-spras,

maktx TYPE makt-maktx,

END OF it_makt.

DATA:BEGIN OF it_marc OCCURS 0,

matnr TYPE marc-matnr,

werks TYPE marc-werks,

stawn TYPE marc-stawn,

herkl TYPE marc-herkl,

END OF it_marc.

DATA:BEGIN OF it_mbew OCCURS 0,

matnr TYPE mbew-matnr,

bwkey TYPE mbew-bwkey,

stprs TYPE mbew-stprs,

END OF it_mbew.

DATA: BEGIN OF it_t001w OCCURS 0,

land1 TYPE t001w-land1,

spras TYPE t001w-spras,

END OF it_t001w.

DATA:BEGIN OF it_ext OCCURS 0,

matnr TYPE mard-matnr,

werks TYPE mard-werks,

ersda TYPE mard-ersda,

spras TYPE t001w-spras,

END OF it_ext.

DATA:BEGIN OF it_t001w1 OCCURS 0,

werks TYPE t001w-werks,

spras TYPE t001w-spras,

END OF it_t001w1.

DATA : n(4) TYPE n VALUE '0'.

DATA: BEGIN OF it_marc1 OCCURS 0,

matnr TYPE marc-matnr,

werks TYPE marc-werks,

END OF it_marc1.

DATA: it_fieldcat TYPE slis_t_fieldcat_alv,

fcat1 TYPE slis_fieldcat_alv,

wa_fieldcat TYPE slis_fieldcat_alv,

gt_list_top_of_page TYPE slis_t_listheader,

disvariant LIKE disvariant,

d_disvariant LIKE disvariant,

lv_repid LIKE sy-repid,

ls_layout TYPE slis_layout_alv.

----

SELECTION SCREEN *

----

SELECTION-SCREEN BEGIN OF BLOCK blk WITH FRAME TITLE text-001.

SELECT-OPTIONS: s_matnr FOR mara-matnr,

s_werks FOR marc-werks OBLIGATORY,

s_ersda FOR mard-ersda OBLIGATORY.

PARAMETERS: p_check AS CHECKBOX, "For Materials Extended

p_chang AS CHECKBOX. "For Master Data Changed

SELECTION-SCREEN END OF BLOCK blk.

----

AT SELECTION-SCREEN *

----

AT SELECTION-SCREEN ON s_matnr.

validations for Material on selection-screen

SELECT SINGLE matnr INTO mara-matnr FROM mara WHERE matnr IN s_matnr.

validations for Plant on selection-screen

*AT SELECTION-SCREEN ON s_werks.

IF NOT s_werks IS INITIAL.
SELECT werks
FROM t001w UP TO 1 ROWS
INTO t001w-werks
WHERE werks IN s_werks.
ENDSELECT.
IF sy-subrc NE 0.
SET CURSOR FIELD 'S_WERKS-LOW'.
MESSAGE e999 WITH text-002.
ENDIF.
ENDIF.

----

START-OF-SELECTION *

----

START-OF-SELECTION.

For Displaying Material Extended

IF p_check EQ 'X' AND

p_chang EQ space.

PERFORM mat-extended.

PERFORM dis_matext.

For Displaying Master Data Changed

ELSEIF p_chang EQ 'X' AND

p_check EQ space.

PERFORM mat-changed.

PERFORM dis_matext.

For Displaying Material Extended and Master Data Changed

ELSEIF p_check EQ 'X'

AND p_chang EQ 'X'.

PERFORM mat-extended.

PERFORM mat-changed.

LOOP AT it_out.

MOVE-CORRESPONDING it_out TO it_final.

APPEND it_final.

CLEAR it_final.

ENDLOOP.

APPEND LINES OF it_outm TO it_final.

PERFORM dis_matext.

ENDIF.

&----

*& Form MAT-EXTENDED

&----

text

----

--> p1 text
<-- p2 text

----

FORM mat-extended.

SELECT matnr werks ersda FROM mard INTO CORRESPONDING FIELDS OF

TABLE it_ext

WHERE matnr IN s_matnr

AND werks IN s_werks

AND ersda IN s_ersda.

IF NOT it_ext[] IS INITIAL.

SORT it_ext.

DELETE ADJACENT DUPLICATES FROM it_ext COMPARING matnr werks ersda.

For getting Material discription based on the Plant.

SELECT werks spras FROM t001w INTO TABLE it_t001w1

WHERE werks IN s_werks.

LOOP AT it_ext.

READ TABLE it_t001w1 WITH KEY werks = it_ext-werks.

IF sy-subrc EQ 0.

it_ext-spras = it_t001w1-spras.

MODIFY it_ext.

ENDIF.

ENDLOOP.

Appending Modification 'A' for every Extended Material.

LOOP AT it_ext.

it_out-matnr = it_ext-matnr.

it_out-werks = it_ext-werks.

it_out-spras = it_ext-spras.

it_out-mod = 'A'.

APPEND it_out.

CLEAR it_out.

ENDLOOP.

SORT it_out.

For getting Material Discription based on the Plants Language

IF NOT it_ext[] IS INITIAL.

SORT it_ext.

SELECT matnr spras maktx FROM makt INTO TABLE it_makt

FOR ALL ENTRIES IN it_ext

WHERE matnr = it_ext-matnr

AND spras = it_ext-spras.

ENDIF.

Getting data into Internal tables.

IF NOT it_out[] IS INITIAL.

SELECT matnr brgew gewei FROM mara INTO TABLE it_mara

FOR ALL ENTRIES IN it_out

WHERE matnr = it_out-matnr.

SELECT matnr werks stawn herkl FROM marc INTO TABLE it_marc

FOR ALL ENTRIES IN it_out

WHERE matnr = it_out-matnr

AND werks IN s_werks.

SELECT matnr bwkey stprs FROM mbew INTO TABLE it_mbew

FOR ALL ENTRIES IN it_out

WHERE matnr = it_out-matnr

AND bwkey = it_out-werks.

ENDIF.

For getting Country of Origin based on Selection screen Plant.

IF NOT it_marc[] IS INITIAL.

SELECT land1 spras FROM t001w INTO TABLE it_t001w

FOR ALL ENTRIES IN it_marc

WHERE land1 = it_marc-herkl.

ENDIF.

Reading data to final internal table .

LOOP AT it_out.

READ TABLE it_makt WITH KEY matnr = it_out-matnr

spras = it_out-spras BINARY SEARCH.

IF sy-subrc EQ 0.

it_out-maktx = it_makt-maktx.

ENDIF.

READ TABLE it_mara WITH KEY matnr = it_out-matnr BINARY SEARCH.

IF sy-subrc EQ 0.

it_out-brgew = it_mara-brgew.

it_out-gewei = it_mara-gewei.

ENDIF.

READ TABLE it_marc WITH KEY matnr = it_out-matnr

werks = it_out-werks

BINARY SEARCH.

IF sy-subrc EQ 0.

it_out-stawn = it_marc-stawn.

it_out-herkl = it_marc-herkl.

ENDIF.

READ TABLE it_mbew WITH KEY matnr = it_out-matnr BINARY SEARCH.

IF sy-subrc EQ 0.

it_out-stprs = it_mbew-stprs.

it_out-werks = it_mbew-bwkey.

ENDIF.

MODIFY it_out.

ENDLOOP.

SORT it_out.

ENDIF.

ENDFORM. " MAT-EXTENDED

&----

*& Form mat-changed

&----

text

----

--> p1 text
<-- p2 text

----

FORM mat-changed.

For getting changed materials which are extended to selected Plant.

SELECT a_{objectclas a}objectid a_{changenr b}werks

INTO TABLE it_change

FROM cdhdr AS a INNER JOIN marc AS b ON b_{matnr = a}objectid

WHERE a~objectclas EQ 'MATERIAL' AND

a~objectid IN s_matnr AND

a~udate IN s_ersda AND

( a~tcode NE 'MM01' OR

a~tcode NE 'ZAUTOEXT' ) AND

b~werks IN s_werks.

Getting the change numbers,fnames and new values from CDPOS for
selected changed materials

IF NOT it_change[] IS INITIAL.

SELECT objectclas objectid changenr tabname tabkey fname

chngind value_new FROM cdpos

INTO TABLE it_cdpos

FOR ALL ENTRIES IN it_change

WHERE objectclas EQ it_change-objectclas AND

objectid EQ it_change-objectid AND

changenr EQ it_change-changenr AND

fname IN ('MAKTX','BRGEW','GEWEI',

'STAWN','HERKL','STPRS').

ENDIF.

SORT it_change DESCENDING.

SORT it_cdpos DESCENDING BY objectclas objectid fname changenr.

DATA : v_objectclas LIKE cdhdr-objectclas,

v_objectid LIKE cdhdr-objectid,

v_fname LIKE cdpos-fname.

*For getting Material Description based on Selected Plants Language

LOOP AT it_cdpos.

DATA flag TYPE c.

CLEAR flag.

CLEAR t001w.

SELECT SINGLE *

FROM t001w

WHERE werks IN s_werks.

IF sy-subrc = 0.

IF it_cdpos-fname = 'MAKTX' AND

t001w-spras+0(1) = it_cdpos-tabkey.

it_cdpos-value_new = it_cdpos-value_new.

flag = 'X'.

ELSEIF flag NE 'X' AND it_cdpos-fname = 'MAKTX'.

it_cdpos-value_new = ' '.

ENDIF.

MODIFY it_cdpos TRANSPORTING value_new .

ENDIF.

For getting changed Commit code based on the Selection screen Plant

CLEAR flag.

IF sy-subrc = 0.

IF it_cdpos-fname = 'STAWN' AND

t001w-werks = it_cdpos-tabkey+21(4).

it_cdpos-value_new = it_cdpos-value_new.

flag = 'X'.

ELSEIF flag NE 'X' AND it_cdpos-fname = 'STAWN'.

it_cdpos-value_new = ' '.

ENDIF.

MODIFY it_cdpos TRANSPORTING value_new.

ENDIF.

For getting changed Country of origin based on Selection screen Plant

CLEAR flag.

IF sy-subrc = 0.

IF it_cdpos-fname = 'HERKL' AND

t001w-werks = it_cdpos-tabkey+21(4).

it_cdpos-value_new = it_cdpos-value_new.

flag = 'X'.

ELSEIF flag NE 'X' AND it_cdpos-fname = 'HERKL'.

it_cdpos-value_new = ' '.

ENDIF.

MODIFY it_cdpos TRANSPORTING value_new.

ENDIF.

For getting changed Standard price based on Selection screen Plant

IF sy-subrc EQ 0.

IF it_cdpos-fname = 'STPRS' AND

t001w-werks = it_cdpos-tabkey+21(4).

SELECT SINGLE * FROM mbew

WHERE matnr EQ it_cdpos-objectid AND

bwkey EQ it_cdpos-tabkey+21(4).

IF sy-subrc EQ 0.

it_cdpos-value_new = mbew-stprs.

MODIFY it_cdpos TRANSPORTING value_new.

ENDIF.

ENDLOOP.

LOOP AT it_cdpos.

CLEAR t001w.

SELECT SINGLE *

FROM t001w

WHERE werks IN s_werks.

IF it_cdpos-objectclas EQ v_objectclas AND

it_cdpos-objectid EQ v_objectid AND

it_cdpos-fname EQ v_fname AND

it_cdpos-tabkey NE t001w-spras.

ELSE.

it_out2-matnr = it_cdpos-objectid.

it_out2-fname = it_cdpos-fname.

it_out2-tabkey = it_cdpos-tabkey.

it_out2-value = it_cdpos-value_new.

IF it_cdpos-fname EQ 'STPRS'.

CLEAR it_out2-value.

SELECT SINGLE * FROM mbew

WHERE matnr EQ it_cdpos-objectid AND

bwkey EQ it_cdpos-tabkey+21(4).

IF sy-subrc EQ 0 AND t001w-werks EQ mbew-bwkey.

it_out2-value = it_cdpos-value_new.

ENDIF.

APPEND it_out2.

CLEAR it_out2.

ENDIF.

v_objectclas = it_cdpos-objectclas.

v_objectid = it_cdpos-objectid.

v_fname = it_cdpos-fname.

ENDLOOP.

Appending Modification 'C' for every Changed Material.

LOOP AT it_out2.

it_outm-matnr = it_out2-matnr.

it_outm-mod = 'C'.

APPEND it_outm.

CLEAR it_outm.

ENDLOOP.

DELETE ADJACENT DUPLICATES FROM it_outm COMPARING matnr.

Checking with changed fname internal teble is modified.

SORT IT_OUT2 BY MATNR FNAME .

DATA: flg TYPE i VALUE 0,

v_matnr(18) TYPE c .

CLEAR v_matnr.

LOOP AT it_out2.

IF v_matnr NE it_out2-matnr.

flg = 0.

ENDIF.

READ TABLE it_outm WITH KEY matnr = it_out2-matnr.

IF sy-subrc EQ 0.

IF NOT it_out2-fname IS INITIAL.

IF it_out2-fname = 'MAKTX'.

IF flg EQ 0.

it_outm-maktx = it_out2-value.

MODIFY it_outm INDEX sy-tabix.

flg = 1.

ENDIF.

IF NOT it_out2-fname IS INITIAL.

IF it_out2-fname = 'BRGEW'.

SELECT SINGLE * FROM mara

WHERE matnr EQ it_out2-matnr.

IF sy-subrc EQ 0.

it_outm-brgew = mara-brgew.

MODIFY it_outm INDEX sy-tabix.

ENDIF.

IF NOT it_out2-fname IS INITIAL.

IF it_out2-fname = 'GEWEI'.

it_outm-gewei = it_out2-value.

MODIFY it_outm INDEX sy-tabix.

ENDIF.

IF NOT it_out2-fname IS INITIAL.

IF it_out2-fname = 'STAWN'.

it_outm-stawn = it_out2-value.

MODIFY it_outm INDEX sy-tabix.

ENDIF.

IF NOT it_out2-fname IS INITIAL.

IF it_out2-fname = 'HERKL'.

it_outm-herkl = it_out2-value.

MODIFY it_outm INDEX sy-tabix.

ENDIF.

IF NOT it_out2-fname IS INITIAL.

IF it_out2-fname = 'STPRS'.

it_outm-stprs = it_out2-value.

MODIFY it_outm INDEX sy-tabix.

ENDIF.

CLEAR v_matnr.

v_matnr = it_out2-matnr.

ENDLOOP.

If chages are not happen to the s_matnr,it should not dispaly.

DELETE it_outm WHERE maktx EQ '' AND

brgew EQ '' AND

gewei EQ '' AND

stawn EQ '' AND

herkl EQ '' AND

stprs EQ ''.

ENDFORM. " mat-changed

&----

*& Form DIS_MATEXT

&----

text

----

--> p1 text
<-- p2 text

----

FORM dis_matext.

lv_repid = sy-repid.

DATA: it_fieldcat TYPE slis_t_fieldcat_alv,

wa_fieldcat TYPE slis_fieldcat_alv,

is_layout TYPE slis_layout_alv,

disvariant LIKE disvariant,

d_disvariant LIKE disvariant,

d_repid LIKE sy-repid.

CLEAR disvariant.

disvariant-report = sy-repid.

disvariant-username = sy-uname.

d_disvariant = disvariant.

Dispalying Material Extended data

IF p_check EQ 'X'

AND p_chang EQ space.

CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'

EXPORTING

i_program_name = disvariant-report

i_structure_name = 'ZMEC'

i_inclname = disvariant-report

CHANGING

ct_fieldcat = it_fieldcat

EXCEPTIONS

inconsistent_interface = 1

program_error = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

LOOP AT it_fieldcat INTO wa_fieldcat.

IF wa_fieldcat-fieldname EQ 'MATNR'.

wa_fieldcat-seltext_l = 'Material number'.

wa_fieldcat-seltext_m = 'Material number'.

wa_fieldcat-seltext_s = 'Material number'.

wa_fieldcat-reptext_ddic = 'Material number'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'MAKTX'.

wa_fieldcat-seltext_l = 'Material Description'.

wa_fieldcat-seltext_m = 'Material Description'.

wa_fieldcat-seltext_s = 'Material Description'.

wa_fieldcat-reptext_ddic = 'Material Description'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'BRGEW'.

wa_fieldcat-seltext_l = 'Gross weight'.

wa_fieldcat-seltext_m = 'Gross weight'.

wa_fieldcat-seltext_s = 'Gross weight'.

wa_fieldcat-reptext_ddic = 'Gross weight'.

wa_fieldcat-quantity = 'X'.

wa_fieldcat-no_zero = 'X'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'GEWEI'.

wa_fieldcat-seltext_l = 'Weight Unit'.

wa_fieldcat-seltext_m = 'Weight Unit'.

wa_fieldcat-seltext_s = 'Weight Unit'.

wa_fieldcat-reptext_ddic = 'Weight Unit'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'STAWN'.

wa_fieldcat-seltext_l = text-003.

wa_fieldcat-seltext_m = text-003.

wa_fieldcat-seltext_s = text-003.

wa_fieldcat-reptext_ddic = text-003.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'STPRS'.

wa_fieldcat-seltext_l = 'Standard price'.

wa_fieldcat-seltext_m = 'Standard price'.

wa_fieldcat-seltext_s = 'Standard price'.

wa_fieldcat-reptext_ddic = 'Standard price'.

wa_fieldcat-ddictxt = 'L'.

wa_fieldcat-no_zero = 'X'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'MOD'.

wa_fieldcat-seltext_l = 'Modification type'.

wa_fieldcat-seltext_m = 'Modification type'.

wa_fieldcat-seltext_s = 'Modification type'.

wa_fieldcat-reptext_ddic = 'Modification type'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

DELETE it_fieldcat WHERE fieldname = 'WERKS'.

DELETE it_fieldcat WHERE fieldname = 'SPRAS'.

ENDLOOP.

if it_outm[] is initial.
MESSAGE w999 WITH text-004.
endif.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = disvariant-report

it_fieldcat = it_fieldcat

is_layout = ls_layout

i_save = 'A'

TABLES

t_outtab = it_out

EXCEPTIONS

program_error = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Displaying Master data Changed

ELSEIF p_check EQ space

AND p_chang EQ 'X' .

CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'

EXPORTING

i_program_name = disvariant-report

i_structure_name = 'ZMEC'

i_inclname = disvariant-report

CHANGING

ct_fieldcat = it_fieldcat

EXCEPTIONS

inconsistent_interface = 1

program_error = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

LOOP AT it_fieldcat INTO wa_fieldcat.

IF wa_fieldcat-fieldname EQ 'MATNR'.

wa_fieldcat-seltext_l = 'Material number'.

wa_fieldcat-seltext_m = 'Material number'.

wa_fieldcat-seltext_s = 'Material number'.

wa_fieldcat-reptext_ddic = 'Material number'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'MAKTX'.

wa_fieldcat-seltext_l = 'Material Description'.

wa_fieldcat-seltext_m = 'Material Description'.

wa_fieldcat-seltext_s = 'Material Description'.

wa_fieldcat-reptext_ddic = 'Material Description'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'BRGEW'.

wa_fieldcat-seltext_l = 'Gross Weight'.

wa_fieldcat-seltext_m = 'Gross Weight'.

wa_fieldcat-seltext_s = 'GROSS WEIGHT'.

wa_fieldcat-reptext_ddic = 'GROSS WEIGHT'.

wa_fieldcat-quantity = 'X'.

wa_fieldcat-no_zero = 'X'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'GEWEI'.

wa_fieldcat-seltext_l = 'Weight Unit'.

wa_fieldcat-seltext_m = 'Weight Unit'.

wa_fieldcat-seltext_s = 'Weight Unit'.

wa_fieldcat-reptext_ddic = 'Weight Unit'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'STAWN'.

wa_fieldcat-seltext_l = text-003.

wa_fieldcat-seltext_m = text-003.

wa_fieldcat-seltext_s = text-003.

wa_fieldcat-reptext_ddic = text-003.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'STPRS'.

wa_fieldcat-seltext_l = 'Standard price'.

wa_fieldcat-seltext_m = 'Standard price'.

wa_fieldcat-seltext_s = 'Standard price'.

wa_fieldcat-reptext_ddic = 'Standard price'.

wa_fieldcat-ddictxt = 'L'.

wa_fieldcat-no_zero = 'X'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'MOD'.

wa_fieldcat-seltext_l = 'Modification type'.

wa_fieldcat-seltext_m = 'Modification type'.

wa_fieldcat-seltext_s = 'Modification type'.

wa_fieldcat-reptext_ddic = 'Modification type'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

DELETE it_fieldcat WHERE fieldname = 'WERKS'.

DELETE it_fieldcat WHERE fieldname = 'SPRAS'.

ENDLOOP.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = disvariant-report

it_fieldcat = it_fieldcat

is_layout = ls_layout

i_save = 'A'

TABLES

t_outtab = it_outm

EXCEPTIONS

program_error = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Displaying both Material Extended and Changed data.

ELSEIF p_check EQ 'X'

AND p_chang EQ 'X'.

CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'

EXPORTING

i_program_name = disvariant-report

i_structure_name = 'ZMEC'

i_inclname = disvariant-report

CHANGING

ct_fieldcat = it_fieldcat

EXCEPTIONS

inconsistent_interface = 1

program_error = 2

OTHERS = 3.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

LOOP AT it_fieldcat INTO wa_fieldcat.

IF wa_fieldcat-fieldname EQ 'MATNR'.

wa_fieldcat-seltext_l = 'Material number'.

wa_fieldcat-seltext_m = 'Material number'.

wa_fieldcat-seltext_s = 'Material number'.

wa_fieldcat-reptext_ddic = 'Material number'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'MAKTX'.

wa_fieldcat-seltext_l = 'Material Description'.

wa_fieldcat-seltext_m = 'Material Description'.

wa_fieldcat-seltext_s = 'Material Description'.

wa_fieldcat-reptext_ddic = 'Material Description'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'BRGEW'.

wa_fieldcat-seltext_l = 'Gross weight'.

wa_fieldcat-seltext_m = 'Gross weight'.

wa_fieldcat-seltext_s = 'Gross weight'.

wa_fieldcat-reptext_ddic = 'Gross weight'.

wa_fieldcat-quantity = 'X'.

wa_fieldcat-no_zero = 'X'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'GEWEI'.

wa_fieldcat-seltext_l = 'Weight Unit'.

wa_fieldcat-seltext_m = 'Weight Unit'.

wa_fieldcat-seltext_s = 'Weight Unit'.

wa_fieldcat-reptext_ddic = 'Weight Unit'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'STAWN'.

wa_fieldcat-seltext_l = text-003.

wa_fieldcat-seltext_m = text-003.

wa_fieldcat-seltext_s = text-003.

wa_fieldcat-reptext_ddic = text-003.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'STPRS'.

wa_fieldcat-seltext_l = 'Standard price'.

wa_fieldcat-seltext_m = 'Standard price'.

wa_fieldcat-seltext_s = 'Standard price'.

wa_fieldcat-reptext_ddic = 'Standard price'.

wa_fieldcat-ddictxt = 'L'.

wa_fieldcat-no_zero = 'X'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

IF wa_fieldcat-fieldname EQ 'MOD'.

wa_fieldcat-seltext_l = 'Modification type'.

wa_fieldcat-seltext_m = 'Modification type'.

wa_fieldcat-seltext_s = 'Modification type'.

wa_fieldcat-reptext_ddic = 'Modification type'.

wa_fieldcat-ddictxt = 'L'.

MODIFY it_fieldcat FROM wa_fieldcat.

ENDIF.

DELETE it_fieldcat WHERE fieldname = 'WERKS'.

DELETE it_fieldcat WHERE fieldname = 'SPRAS'.

ENDLOOP.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = disvariant-report

it_fieldcat = it_fieldcat

is_layout = ls_layout

i_save = 'A'

TABLES

t_outtab = it_final

EXCEPTIONS

program_error = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

ENDFORM. " DIS_MATEXT

Re: Performance tune

Former Member — Tue, 01 May 2007 16:13:34 GMT

Since you are using the CDHDR and CDPOS tables generally these tables consume lot of time.

But still you can join the tables MARD,MAKT,MARA,MARC,MBEW into a single select since all these tables are material related and the key field in them is the MATNR field.

And try to remove the select singles in the loops of ht eCDPOS, and instead use READ staments for the corresponding tables.

reward if useful

regards

anji

Re: Performance tune

Former Member — Tue, 01 May 2007 16:15:42 GMT

hi,

<b> instead of IF stm try to use an CASE stm </b>

SELECT matnr werks ersda FROM mard INTO <b><table-field1> <table-field2> <table-field3></b> WHERE matnr IN s_matnr

AND werks IN s_werks

AND ersda IN s_ersda.

try to write the selct stm lke this try to speciy the fields if u have smaller no of fields.

reward if helpful

ravi

Re: Performance tune

Former Member — Tue, 01 May 2007 16:21:15 GMT

IF NOT it_out[] IS INITIAL.

SELECT matnr brgew gewei FROM mara INTO TABLE it_mara

FOR ALL ENTRIES IN it_out

WHERE matnr = it_out-matnr.

SELECT matnr werks stawn herkl FROM marc INTO TABLE it_marc

FOR ALL ENTRIES IN it_out

WHERE matnr = it_out-matnr

AND werks IN s_werks.

SELECT matnr bwkey stprs FROM mbew INTO TABLE it_mbew

FOR ALL ENTRIES IN it_out

WHERE matnr = it_out-matnr

AND bwkey = it_out-werks.

ENDIF.

Please use inner join for the above querry.

Again these tables CDHDR and CDPOS are related with change history and will be huge and cant be avoided. Try to avoid select statements within Loop and Endloop.

Shreekant.

Re: Performance tune

Clemenss — Tue, 01 May 2007 21:11:56 GMT

Hi karthik,

as already mentioned: The main problems are caused by huge change tables CDHDR and CDPOS.

You can verify with performance trace, on the list click toolbox icon to see that caused by three IN operators a full table scan will be performed.

As you already have the values in table it_ext, try FOR ALL ENTRIES. I tried to change your select statement also making it more readable - you don't need alias a or b. An alias is required only if you catch data from the same table using different conditions.

Hope this will increase performance: [used Code button above to keep formatting]


SELECT 
  CDHDR~objectclas 
  CDHDR~objectid 
  CDHDR~changenr 
  MARC~werks                                "No need  
  INTO TABLE it_change
  FROM cdhdr 
  INNER JOIN marc                           "No need
    ON MARC~matnr = CDHDR~objectid          "No need
  FOR ALL ENTRIES IN it_ext
  WHERE CDHDR~objectclas  = 'MATERIAL' 
    AND CDHDR~objectid    = it_ext-matnr 
    AND  CDHDR~udate      = it_ext-ersda 
    AND ( CDHDR~tcode     <> 'MM01' OR
          CDHDR~tcode     <> 'ZAUTOEXT' ) 
    AND MARC~werks        = it_ext-werks    "No need
    .

But still I don't understand fully what the join on MARC is good for: It will just multiply the found records for each change number with the number of plants the material is maintained in. But you already know this. Just delete/disable the lines I marked with "No need and it will give you a boost. I did not analyze the complete merging logic, but you have all information in it_change you need to go ahead.

Regards

Clemens