help me

Former Member · ‎2007 Sep 10

Hi experts

My program has following performance.

Pls mark the part of coding where changes needed to improve performace of the report.

ABAP : 205,610 = 1.3%

Database : 15,713,653 = 98.4%

System : 57,322 = 0.4%

I attached my coding also.

Regards

Rajaram

REPORT ZSAB_SO1.

TABLES : mkpf,mseg,t247.

TYPE-POOLS : slis.

DATA : fcat TYPE slis_t_fieldcat_alv,

ls_fcat LIKE LINE OF fcat,

fheader TYPE TABLE OF slis_listheader WITH NON-UNIQUE DEFAULT KEY

WITH HEADER LINE INITIAL SIZE 0.

DATA : i_repid LIKE sy-repid,

gs_layout TYPE slis_layout_alv,

flag type n.

*Internal Table

DATA : BEGIN OF itab OCCURS 0,

mblnr like mseg-mblnr,

budat like mkpf-budat,

KDAUF like mseg-KDAUF,

KDPOS like mseg-KDPOS,

matnr like mseg-matnr,

WERKS like mseg-werks,

ERFMG like mseg-ERFMG,

ERFME like mseg-ERFME,

BWART like mseg-BWART,

maktx like makt-maktx,

WMENG like vbep-WMENG,

VRKME like vbep-VRKME,

end of itab.

DATA : BEGIN OF itab1 OCCURS 0,

mblnr like mseg-mblnr,

kdauf(90) type c,

kdpos like mseg-kdpos,

matnr like mseg-matnr,

werks like mseg-werks,

erfmg like mseg-erfmg,

erfme like mseg-erfme,

bwart like mseg-BWART,

budat like mkpf-budat,

maktx like makt-maktx,

WMENG like vbep-WMENG,

ABGRU like vbap-ABGRU,

status(10) type c,

lights,

end of itab1.

DATA : BEGIN OF it_final OCCURS 0,

mblnr like mseg-mblnr,

lights,

budat like mkpf-budat,

KDAUF like mseg-KDAUF,

kdauf(90) type c,

KDPOS like mseg-KDPOS,

matnr like mseg-matnr,

WERKS like mseg-werks,

ERFMG like mseg-ERFMG,

ERFME like mseg-ERFME,

BWART like mseg-BWART,

maktx like makt-maktx,

WMENG like vbep-WMENG,

ABGRU like vbap-ABGRU,

AEDAT like vbap-AEDAT,

status(10) type c,

month(10) type c,

ltx LIKE t247-ltx,

ktx LIKE t247-ktx,

end of it_final.

DATA : begin of ipos occurs 0,

OBJECTCLAS like cdpos-OBJECTCLAS,

OBJECTID like cdpos-OBJECTID,

CHANGENR like cdpos-CHANGENR,

TABNAME like cdpos-TABNAME,

TABKEY like cdpos-TABKEY,

FNAME like cdpos-FNAME,

CHNGIND like cdpos-CHNGIND,

VALUE_NEW like cdpos-VALUE_NEW,

VALUE_OLD like cdpos-VALUE_OLD,

kda(20) type n,

pos(20) type n,

UDATE like cdhdr-UDATE,

UTIME like cdhdr-UTIME,

CHANGENR1 like cdhdr-CHANGENR,

end of ipos.

DATA : begin of it1 occurs 0,

OBJECTCLAS like cdpos-OBJECTCLAS,

OBJECTID like cdpos-OBJECTID,

CHANGENR like cdpos-CHANGENR,

TABNAME like cdpos-TABNAME,

TABKEY like cdpos-TABKEY,

FNAME like cdpos-FNAME,

CHNGIND like cdpos-CHNGIND,

VALUE_NEW like cdpos-VALUE_NEW,

VALUE_OLD like cdpos-VALUE_OLD,

UDATE like cdhdr-UDATE,

UTIME like cdhdr-UTIME,

TCODE like cdhdr-TCODE,

CHANGENR1 like cdhdr-CHANGENR,

matnr like vbap-matnr,

BWART like mseg-BWART,

ERFMG like mseg-ERFMG,

werks like vbap-werks,

kda(90) type c,

pos like mseg-kdpos,

end of it1.

DATA : BEGIN OF IT2 OCCURS 0,

OBJECTCLAS LIKE CDHDR-OBJECTCLAS,

OBJECTID LIKE CDHDR-OBJECTID,

CHANGENR LIKE CDHDR-CHANGENR,

UDATE LIKE CDHDR-UDATE,

UTIME LIKE CDHDR-UTIME,

TCODE LIKE CDHDR-TCODE,

TABNAME like cdpos-TABNAME,

TABKEY like cdpos-TABKEY,

FNAME like cdpos-FNAME,

CHNGIND like cdpos-CHNGIND,

VALUE_NEW like cdpos-VALUE_NEW,

VALUE_OLD like cdpos-VALUE_OLD,

CHANGENR1 like cdhdr-CHANGENR,

matnr like vbap-matnr,

BWART like mseg-BWART,

ERFMG like mseg-ERFMG,

werks like vbap-werks,

kda(90) type c,

pos like mseg-kdpos,

END OF IT2.

data : begin of itab10 occurs 0,

mblnr like mkpf-mblnr,

budat like mkpf-budat,

end of itab10.

*data : it2 like it1 occurs 0 with header line.

data : itab2 like itab1 occurs 0 with header line.

data : e_itab2 like line of itab2.

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

SELECT-OPTIONS: zdate FOR mkpf-budat OBLIGATORY.

SELECT-OPTIONS: zpl FOR mseg-WERKS OBLIGATORY.

SELECTION-SCREEN END OF BLOCK bk1.

start-of-selection.

gs_layout-colwidth_optimize = 'X'.

Select statement for read all the delivery document

SELECT mseg~mblnr

mkpf~budat

mseg~kdauf

mseg~kdpos

mseg~matnr

mseg~werks

mseg~erfmg

mseg~erfme

mseg~BWART

from mseg

inner join

mkpf on mkpf_{mblnr EQ mseg}mblnr

into table itab

where mkpf~budat in zdate and

mseg~werks in zpl and

mseg~kdauf ne ' ' and

( mseg~BWART = '601' or

mseg~bwart = '602').

*if sy-subrc = 0.

*

*loop at itab.

if zpl-low is not initial and zpl-high is not initial.
if ( itab-werks not between zpl-low and zpl-high ).
delete itab.
endif.
endif.

*

*if zpl-high is initial and itab-werks <> zpl-low.

delete it1.

*endif.

*endloop.

*

*endif.

select cdpos~OBJECTCLAS

cdpos~OBJECTID

cdpos~CHANGENR

cdpos~TABNAME

cdpos~TABKEY

cdpos~FNAME

cdpos~CHNGIND

cdpos~VALUE_NEW

cdpos~VALUE_OLD

from cdpos into table it1

where OBJECTCLAS = 'VERKBELEG' and

tabname = 'VBAP' and

fname = 'ABGRU'.

if zdate-high is not initial.

select OBJECTCLAS

OBJECTID

CHANGENR

UDATE

UTIME

TCODE

from cdhdr

into table it2

for all entries in it1

where

OBJECTCLAS = 'VERKBELEG' and

TCODE = 'VA02' and

OBJECTID = it1-OBJECTID and

CHANGENR = it1-CHANGENR and

UDATE IN (ZDATE-LOW , ZDATE-HIGH).

( UDATE BETWEEN ZDATE-LOW AND ZDATE-HIGH ).

else.

select OBJECTCLAS

OBJECTID

CHANGENR

UDATE

UTIME

TCODE

from cdhdr

into table it2

for all entries in it1

where

OBJECTCLAS = 'VERKBELEG' and

TCODE = 'VA02' and

OBJECTID = it1-OBJECTID and

CHANGENR = it1-CHANGENR and

( UDATE = ZDATE-LOW ).

endif.

loop at it2.

read table it1 with key changenr = it2-changenr.

if sy-subrc = 0.

MOVE-CORRESPONDING IT1 TO IT2.

modify it2.

endif.

endloop.

loop at it2.

it2-kda = it2-tabkey+3(10).

it2-pos = it2-tabkey+14(6).

*it1-kda = it1-tabkey+5(8).

*it1-pos = it1-tabkey+14(6).

modify it2.

endloop.

delete adjacent duplicates from it2 comparing kda pos.

loop at it2.

select single vbeln

posnr

matnr

WERKS

from vbap

into (it2-kda , it2-pos , it2-matnr , it2-werks)

where vbeln = it2-kda and posnr = it2-pos.

modify it2.

endloop.

loop at it2.

if zpl-low is not initial and zpl-high is not initial.

if ( it2-werks not between zpl-low and zpl-high ).

delete it2.

else.

move it2-kda to itab-kdauf.

move it2-pos to itab-kdpos.

move it2-matnr to itab-matnr.

move it2-bwart to itab-bwart.

move it2-erfmg to itab-erfmg.

move it2-werks to itab-werks.

move it2-UDATE to itab-budat.

append itab.

flag = 1.

endif.

if zpl-high is initial and it2-werks <> zpl-low.

delete it2.

elseif zpl-high is initial.

if flag = 1.
exit.
endif.

move it2-kda to itab-kdauf.

move it2-pos to itab-kdpos.

move it2-matnr to itab-matnr.

move it2-bwart to itab-bwart.

move it2-erfmg to itab-erfmg.

move it2-werks to itab-werks.

move it2-UDATE to itab-budat.

append itab.

endif.

endloop.

delete adjacent duplicates from it2 comparing kda pos.

*if itab is initial.

*exit.

*endif.

sort itab.

if itab is not initial.

*select a~mblnr

a~kdauf
a~kdpos
a~matnr
a~werks
a~erfmg
a~erfme
a~BWART
b~budat
from mseg as a
inner join mkpf as b on
a_{mblnr = b}mblnr
into TABLE itab1
for all entries in itab
where a~kdauf = itab-kdauf
and a~kdpos = itab-kdpos and
( a_{bwart = '601' or a}bwart = '602' ) and
a~WERKS = itab-werks and
b~budat le itab-budat.

*endif.

select mblnr

kdauf

kdpos

matnr

werks

erfmg

erfme

BWART

from mseg

into TABLE itab1

for all entries in itab

where kdauf = itab-kdauf

and kdpos = itab-kdpos and

( bwart = '601' or bwart = '602' ) and

WERKS = itab-werks.

endif.

loop at itab1.

read table itab with key mblnr = itab1-mblnr kdauf = itab1-kdauf kdpos = itab1-kdpos.

itab1-budat = itab-budat.

modify itab1.

endloop.

select mblnr

budat

from mkpf

into table itab10

for all entries in itab1

where mblnr = itab10-mblnr and

budat le itab1-budat.

loop at itab1.

loop at itab10.

if itab10-mblnr <> itab1-mblnr.

delete itab1.

endif.

endloop.

if sy-subrc = 0.

loop at itab1.

itab1-mblnr = ' '.

itab1-budat = ' '.

modify itab1.

endloop.

loop at itab.

loop at itab1.

if itab-kdauf = itab1-kdauf and itab-kdpos = itab1-kdpos.

flag = 0.

exit.

else.

flag = 1.

endif.

endloop.

if flag = 1.

read table itab1 with key kdauf <> itab-kdauf.

select single VBELN

POSNR

wmeng

from vbep

into (itab-kdauf , itab-kdpos , itab-wmeng)

where VBELN = itab-kdauf and

POSNR = itab-kdpos.

move itab-kdauf to itab1-kdauf.

move itab-kdpos to itab1-kdpos.

move itab-wmeng to itab1-wmeng.

move itab-matnr to itab1-matnr.

move itab-bwart to itab1-bwart.

move itab-erfmg to itab1-erfmg.

move itab-werks to itab1-werks.

append itab1.

endif.

endloop.

else.

loop at itab.

select single VBELN

POSNR

wmeng

VRKME

from vbep

into (itab-kdauf , itab-kdpos , itab-wmeng , itab-VRKME)

where VBELN = itab-kdauf and

POSNR = itab-kdpos.

move itab-kdauf to itab1-kdauf.

move itab-kdpos to itab1-kdpos.

move itab-wmeng to itab1-wmeng.

move itab-matnr to itab1-matnr.

move itab-bwart to itab1-bwart.

move itab-erfmg to itab1-erfmg.

move itab-werks to itab1-werks.

move itab-VRKME to itab1-erfme.

append itab1.

endloop.

endif.

loop at itab1.

if zpl-low is not initial and zpl-high is initial.

if ( itab1-werks NE zpl-low ).

delete itab1.

endif.

endloop.

loop at itab1.

select single maktx

from makt into (itab1-maktx)

where matnr = itab1-matnr.

modify itab1.

endloop.

loop at itab1.

move-corresponding itab1 to itab2.

collect itab2.

endloop.

sort itab2 by kdauf kdpos bwart.

loop at itab2.

read table itab2 into e_itab2 with key kdauf = itab2-kdauf kdpos = itab2-kdpos bwart = '602'.

if sy-subrc eq 0.

delete itab2 index sy-tabix.

itab2-erfmg = itab2-erfmg - e_itab2-erfmg.

endif.

modify itab2.

endloop.

if itab2 is not initial.

select cdpos~OBJECTCLAS

cdpos~OBJECTID

cdpos~CHANGENR

cdpos~TABNAME

cdpos~TABKEY

cdpos~FNAME

cdpos~CHNGIND

cdpos~VALUE_NEW

cdpos~VALUE_OLD

from cdpos into table ipos

for all entries in itab2

where tabname = 'VBAP' and

fname = 'ABGRU' and

OBJECTID = itab2-kdauf.

endif.

if sy-subrc = 0.

loop at ipos.

ipos-kda = ipos-tabkey+5(8).

ipos-pos = ipos-tabkey+14(6).

modify ipos.

endloop.

loop at ipos.

select single CHANGENR UDATE UTIME

from cdhdr

into (ipos-CHANGENR1 , ipos-UDATE , ipos-UTIME)

where CHANGENR = ipos-CHANGENR.

and OBJECTID = ipos-kda.

modify ipos.

endloop.

endif.

loop at itab2.

select single WMENG

from vbep into (itab2-WMENG)

where vbeln = itab2-kdauf and

posnr = itab2-kdpos.

select single ABGRU

from vbap into (itab2-ABGRU)

where vbeln = itab2-kdauf and

posnr = itab2-kdpos.

modify itab2.

endloop.

loop at itab2.

move-corresponding itab2 to it_final.

append it_final.

endloop.

sort itab descending by kdauf kdpos budat descending.

loop at it_final.

read table itab with key kdauf = it_final-kdauf kdpos = it_final-kdpos.

if sy-subrc = 0.

it_final-budat = itab-budat.

modify it_final.

endif.

endloop.

sort it_final by budat descending.

*sort ipos by udate UTIME descending.

sort ipos DESCENDING by kda pos UDATE UTIME .

loop at ipos.

if zdate-high is not initial and ipos-udate GT zdate-high.

delete ipos.

elseif zdate-high is initial and ipos-udate GT zdate-low.

delete ipos.

endif.

endloop.

sort ipos DESCENDING by kda pos UDATE UTIME .

loop at it_final.

read table ipos with key kda = it_final-kdauf pos = it_final-kdpos.

if sy-subrc = 0.

it_final-budat = ipos-udate.

endif.

if it_final-erfmg GE it_final-wmeng.

it_final-status ='COMPLETED'.

it_final-lights = '3'.

else.

if it_final-erfmg LT it_final-wmeng.

if ipos-VALUE_NEW is not initial and ipos-kda = it_final-kdauf and ipos-pos = it_final-kdpos.

it_final-status ='COMPLETED'.

it_final-lights = '3'.

else.

it_final-status ='OPEN'.

it_final-lights = '1'.

endif.

modify it_final.

endloop.

loop at it_final.

*if it_final-werks <> zpl.

*delete it_final.

SELECT SINGLE ktx

ltx

INTO (it_final-ktx , it_final-ltx)

FROM t247

WHERE spras = sy-langu AND

mnr EQ it_final-budat+4(2).

translate it_final-ktx to lower case.

translate it_final-ktx(1) to upper case.

modify it_final.

endloop.

perform edit_mask.

i_repid = sy-repid.

PERFORM fieldcat.

gs_layout-lights_fieldname = 'LIGHTS'.

gs_layout-lights_tabname = 'IT_FINAL'.

ALV GRID

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = i_repid

it_fieldcat = fcat

i_save = 'A'

is_layout = gs_layout

TABLES

t_outtab = it_final

EXCEPTIONS

program_error = 1

OTHERS = 2.

FORM fieldcat .

MOVE sy-repid TO i_repid.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'LIGHTS'.

ls_fcat-outputlen = 10.

ls_fcat-seltext_m = 'STATUS'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'WERKS'.

ls_fcat-outputlen = 4.

ls_fcat-seltext_m = 'Plant'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'BUDAT'.

ls_fcat-outputlen = 10.

ls_fcat-seltext_m = 'Posting Date'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'KTX'.

ls_fcat-outputlen = 3.

ls_fcat-seltext_m = 'Month'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.
ls_fcat-fieldname = 'MBLNR'.
ls_fcat-outputlen = 10.
ls_fcat-seltext_m = 'Document'.
APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'KDAUF'.

ls_fcat-outputlen = 10.

ls_fcat-seltext_m = 'Sales Document'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'KDPOS'.

ls_fcat-outputlen = 4.

ls_fcat-seltext_m = 'Item'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'MATNR'.

ls_fcat-outputlen = 20.

ls_fcat-seltext_m = 'Material'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'MAKTX'.

ls_fcat-outputlen = 40.

ls_fcat-seltext_m = 'Description'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'WMENG'.

ls_fcat-outputlen = 8.

ls_fcat-seltext_m = 'Ord.Qty'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'ERFMG'.

ls_fcat-outputlen = 8.

ls_fcat-seltext_m = 'PGI Done'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'ERFME'.

ls_fcat-outputlen = 3.

ls_fcat-seltext_m = 'UoM'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_FINAL'.

ls_fcat-fieldname = 'STATUS'.

ls_fcat-outputlen = 15.

ls_fcat-seltext_m = 'Status'.

APPEND ls_fcat TO fcat.

ENDFORM. "fieldcat

form edit_mask.

loop at it_final.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

INPUT = it_final-kdauf

IMPORTING

OUTPUT = it_final-kdauf.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

INPUT = it_final-kdpos

IMPORTING

OUTPUT = it_final-kdpos.

modify it_final.

endloop.

endform. "edit_mask

Former Member · ‎2007 Sep 10

Hi,

There are no of places where you have used select statement with in a loop. Try to avoid using select statement within loop. it is better to use read table inside the loop for internal tables

regards

Mukesh

RaymondGiuseppi · ‎2007 Sep 10

Symptom

You read whole CDPOS.

Cause : CDPOS is cluster table , so when reading with selection other than primary keys, you read the whole table.

Solution

Try to read CDHDR first and then read CDPOS for all entries found in CDHDR.

CDOBJECTCL, CDOBJECTV, CDCHANGENR comes from CDHDR, TABNAME = 'VBAP'.

(Also remove those SELECT SINGLE please)

Regards

Former Member · ‎2007 Sep 10

but friend

as cdpos is a cluster table , it does not allow for all entries,

what can i do, tell me.

Regards

Rajaram

RaymondGiuseppi · ‎2007 Sep 10

The little following program works perfectly on my system :

  TABLES: cdhdr, cdpos.

  DATA: t_cdhdr TYPE TABLE OF cdhdr,
        t_cdpos TYPE TABLE OF cdpos.

  SELECT * INTO TABLE t_cdhdr
    FROM cdhdr UP TO 10 ROWS.

  SELECT * INTO TABLE t_cdpos
    FROM cdpos
    FOR ALL ENTRIES IN t_cdhdr
    WHERE objectclas = t_cdhdr-objectclas
      AND objectid = t_cdhdr-objectid
      AND changenr = t_cdhdr-changenr.

  BREAK-POINT.

You cannot use JOINS on cluster, and you can use FOR ALL ENTRIES if you use primary keys of the table.

Regards

Former Member · ‎2007 Sep 10

Hi

in your program you had writen t he select query on joins , that is the problem where the data base load is very high

joining 2 tables is ok but in your program you had used more than 2 tables to join

use for all entries it will be very fast compared to joins

it won't put load on database

reward if usefull

Former Member · ‎2007 Sep 10

hi

good

use MOVE CORRESPONDING statement in all the select statement,than use st05,se30 to check the further error in your code,use extended code checker to check the hidden error in your code.

thanks

mrutyun^

former_member480923 · ‎2007 Sep 10

Hi Change the Select from CDHDR ~ CDPOS by using the following FM: CHANGEDOCUMENT_READ.

This will improve ur performance.

Hope That Helps

Anirban M.

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My Account

My Account

help me