09-10-2007 6:53 AM
hi experts
can you help me regarding performance of this report, how to improve this one.
Thanks in advance.
Regards
Rajaram
report zsab_status
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 : 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 mkpfmblnr EQ msegmblnr
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.
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
amblnr = bmblnr
into TABLE itab1
for all entries in itab
where a~kdauf = itab-kdauf
and a~kdpos = itab-kdpos and
( abwart = '601' or abwart = '602' ) and
a~WERKS = itab-werks and
b~budat le itab-budat.
endif.
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.
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.
endif.
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
09-10-2007 8:22 AM
HI RAJA ,
i ahve seen that ur tables after the select statements are not sorted . u have to sort it and plz dnt use move corresponding and also use binary search with read statment .
Thanks
rohit
reward if helpfull
09-10-2007 8:22 AM
HI RAJA ,
i ahve seen that ur tables after the select statements are not sorted . u have to sort it and plz dnt use move corresponding and also use binary search with read statment .
Thanks
rohit
reward if helpfull
09-10-2007 9:00 AM
Hi Rajaram,
If you incorporate these changes, your code can attain good performance.
1) Avoid using INNER JOIN. Instead of using Inner join, select records from one table first, and then select records from the 2nd table using 'FOR ALL ENTRIES' of the previous fetch.
2) Using 'SELECT' inside LOOP always reduces the performance. So minimize the usage of it. Solution is, first fetch the required entries from the DB table using 'FOR ALL ENTRIES' of the internal table and later modify the internal table.
3) Avoid using LOOP inside another LOOP. Instead
i) use READ statement inside the LOOP, if you require only one specific record
ii) use LOOP with WHERE condition, if you require a set of records satisfying a required condition.
ex: LOOP at itab1 where itab1-vbeln = 100.
.....................
endloop.
regards
Srikanth
09-10-2007 12:31 PM
Hi raja,
The problem is you have selected on MKPF and MSEG twice. These two tables contain hige amount of data.
In your first SELECT try fetching teh data at once - W.r.t Company Code , Plant and ur BUDAT. This will consule less amount of time and u ll not require the second select statenment.
You have used SELECTs inside loops. You take them out. use FOR ALL ENTRIES before going inside the loop and Use READ TABLE when you are comparing and moving the data.
I am sure Doing these two will improve the performance to a large extent.
Cheers
SKC.
09-10-2007 1:01 PM
Hi,
Adding to the above tips on improving performance :-
1. Replace "LIKE" with "TYPE" in all your declarations.
2. Do a Type Decalaration for the internal tables instead of directly declaring them using DATA statement.
3. Before the "DELETE ADJECENTS" statement sort the internal tabel on the field ur doing the delete operation.
4. Thumb Rule:- Never put a Select statement in a LOOP..
5. Avoid loops to the extent u can.
6. An alternative to LOOp/Read statement is :- Ranges.
Do reward points if found helpful
Regards
Dedeepya
09-10-2007 1:05 PM
Hi
by useing joins in the report program it will make the program load on the database
if you use 2 tables in join thats ok
use for all entries when ur useing for all entries then the extraction load from data base is low
reward if usefull