Report program Performance problem

Former Member · ‎10-07-2008

Hi All,

one object is taking 30hr for executing.some one develped this in 1998 but this time it is a big Performance problem.please some one helep what to do i am giving that code.

*--

DOCUMENTATION--

Programe written by : 31.03.1998 .
Purpose : this programe updates the car status into the table zsdtab1
This programe is to be schedule in the backgroud periodically .
Querries can be fired on the table zsdtab1 to get the details of the
Car .
This programe looks at the changes made in the material master from
last updated date and the new entries in material master and updates
the tables zsdtab1 .
Changes in the Sales Order are not taken into account .
To get a fresh data set the value of zupddate in table ZSTATUS as
01.01.1998 . All the data will be refreshed from that date .

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

Program Changed on 23/7/2001 after version upgrade 46b by jyoti
Addition of New tables for Ibase

*----

tables used -

tables : mara , " Material master

ausp , " Characteristics table .

zstatus , " Last updated status table .

zsdtab1 , " Central database table to be maintained .

vbap , " Sales order header table .

vbak , " Sales order item table .

kna1 , " Customer master .

vbrk ,

vbrp ,

bkpf ,

bseg ,

mseg ,

mkpf ,

vbpa ,

vbfa ,

t005t . " Country details tabe .

--

NEW TABLES ADDEDFOR VERSION 4.6B--

tables : ibsymbol ,ibin , ibinvalues .

data : vatinn like ibsymbol-atinn , vatwrt like ibsymbol-atwrt ,

vatflv like ibsymbol-atflv .

*--

types definition--

types : begin of mara_itab_type ,

matnr like mara-matnr ,

cuobf like mara-cuobf ,

end of mara_itab_type ,

begin of ausp_itab_type ,

atinn like ausp-atinn ,

atwrt like ausp-atwrt ,

atflv like ausp-atflv ,

end of ausp_itab_type .

data : mara_itab type mara_itab_type occurs 500 with header line ,

zsdtab1_itab like zsdtab1 occurs 500 with header line ,

ausp_itab type ausp_itab_type occurs 500 with header line ,

last_date type d ,

date type d .

data: length type i.

clear mara_itab . refresh mara_itab .

clear zsdtab1_itab . refresh zsdtab1_itab .

select single zupddate into last_date from zstatus

where programm = 'ZSDDET01' .

select matnr cuobf into (mara_itab-matnr , mara_itab-cuobf) from mara

where mtart eq 'FERT' or mtart = 'ZCBU'.

where MATNR IN MATERIA
and ERSDA IN C_Date
and MTART in M_TYP.

append mara_itab .

endselect .

loop at mara_itab.

clear zsdtab1_itab .

zsdtab1_itab-commno = mara_itab-matnr .

Get the detailed data into internal table ausp_itab .----------->>>

clear ausp_itab . refresh ausp_itab .

--

change starts--

select atinn atwrt atflv into (ausp_itab-atinn , ausp_itab-atwrt ,

ausp_itab-atflv) from ausp

where objek = mara_itab-matnr .

append ausp_itab .

endselect .

clear ausp_itab .

select atinn atwrt atflv into (ausp_itab-atinn , ausp_itab-atwrt ,

ausp_itab-atflv) from ibin as a inner join ibinvalues as b

on a_{in_recno = b}in_recno

inner join ibsymbol as c

on b_{symbol_id = c}symbol_id

where a~instance = mara_itab-cuobf .

append ausp_itab .

endselect .

----

CHANGE ENDS HERE -

sort ausp_itab by atwrt.

loop at ausp_itab .

clear date .

case ausp_itab-atinn .

when '0000000094' .

zsdtab1_itab-model = ausp_itab-atwrt . " model .

when '0000000101' .

zsdtab1_itab-drive = ausp_itab-atwrt . " drive

when '0000000095' .

zsdtab1_itab-converter = ausp_itab-atwrt . "converter

when '0000000096' .

zsdtab1_itab-transmssn = ausp_itab-atwrt . "transmission

when '0000000097' .

zsdtab1_itab-colour = ausp_itab-atwrt . "colour

when '0000000098' .

zsdtab1_itab-ztrim = ausp_itab-atwrt . "trim

when '0000000103' .

*=========Sujit 14-Mar-2006

IF AUSP_ITAB-ATWRT(3) EQ 'WDB' OR AUSP_ITAB-ATWRT(3) EQ 'WDD'
OR AUSP_ITAB-ATWRT(3) EQ 'WDC' OR AUSP_ITAB-ATWRT(3) EQ 'KPD'.
ZSDTAB1_ITAB-CHASSIS_NO = AUSP_ITAB-ATWRT+3(14).
ELSE.
ZSDTAB1_ITAB-CHASSIS_NO = AUSP_ITAB-ATWRT . "chassis no
ENDIF.

zsdtab1_itab-chassis_no = ausp_itab-atwrt . "chassis no

*=========14-Mar-2006

when '0000000166' .

----

25.05.04

length = strlen( ausp_itab-atwrt ).

if length < 15. "***aded by patil

zsdtab1_itab-engine_no = ausp_itab-atwrt . "ENGINE NO

else.

zsdtab1_itab-engine_no = ausp_itab-atwrt+13(14)."Aded on 21.05.04 patil

endif.

----

25.05.04

when '0000000104' .

zsdtab1_itab-body_no = ausp_itab-atwrt . "BODY NO

when '0000000173' . "21.06.98

zsdtab1_itab-cockpit = ausp_itab-atwrt . "COCKPIT NO . "21.06.98

when '0000000102' .

zsdtab1_itab-dest = ausp_itab-atwrt . "destination

when '0000000105' .

zsdtab1_itab-airbag = ausp_itab-atwrt . "AIRBAG

when '0000000110' .

zsdtab1_itab-trailer_no = ausp_itab-atwrt . "TRAILER_NO

when '0000000109' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-fininspdat = date . "FIN INSP DATE

when '0000000108' .

zsdtab1_itab-entrydate = ausp_itab-atwrt . "ENTRY DATE

when '0000000163' .

zsdtab1_itab-regist_no = ausp_itab-atwrt . "REGIST_NO

when '0000000164' .

zsdtab1_itab-mech_key = ausp_itab-atwrt . "MECH_KEY

when '0000000165' .

zsdtab1_itab-side_ab_rt = ausp_itab-atwrt . "SIDE_AB_RT

when '0000000171' .

zsdtab1_itab-side_ab_lt = ausp_itab-atwrt . "SIDE_AB_LT

when '0000000167' .

zsdtab1_itab-elect_key = ausp_itab-atwrt . "ELECT_KEY

when '0000000168' .

zsdtab1_itab-head_lamp = ausp_itab-atwrt . "HEAD_LAMP

when '0000000169' .

zsdtab1_itab-tail_lamp = ausp_itab-atwrt . "TAIL_LAMP

when '0000000170' .

zsdtab1_itab-vac_pump = ausp_itab-atwrt . "VAC_PUMP

when '0000000172' .

zsdtab1_itab-sd_ab_sn_l = ausp_itab-atwrt . "SD_AB_SN_L

when '0000000174' .

zsdtab1_itab-sd_ab_sn_r = ausp_itab-atwrt . "SD_AB_SN_R

when '0000000175' .

zsdtab1_itab-asrhydunit = ausp_itab-atwrt . "ASRHYDUNIT

when '0000000176' .

zsdtab1_itab-gearboxno = ausp_itab-atwrt . "GEARBOXNO

when '0000000177' .

zsdtab1_itab-battery = ausp_itab-atwrt . "BATTERY

when '0000000178' .

zsdtab1_itab-tyretype = ausp_itab-atwrt . "TYRETYPE

when '0000000179' .

zsdtab1_itab-tyremake = ausp_itab-atwrt . "TYREMAKE

when '0000000180' .

zsdtab1_itab-tyresize = ausp_itab-atwrt . "TYRESIZE

when '0000000181' .

zsdtab1_itab-rr_axle_no = ausp_itab-atwrt . "RR_AXLE_NO

when '0000000183' .

zsdtab1_itab-ff_axl_nor = ausp_itab-atwrt . "FF_AXLE_NO_rt

when '0000000182' .

zsdtab1_itab-ff_axl_nol = ausp_itab-atwrt . "FF_AXLE_NO_lt

when '0000000184' .

zsdtab1_itab-drivairbag = ausp_itab-atwrt . "DRIVAIRBAG

when '0000000185' .

zsdtab1_itab-st_box_no = ausp_itab-atwrt . "ST_BOX_NO

when '0000000186' .

zsdtab1_itab-transport = ausp_itab-atwrt . "TRANSPORT

when '0000000106' .

zsdtab1_itab-trackstage = ausp_itab-atwrt . " tracking stage

*----

when '0000000111' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_1 = date . " tracking date for 1.

when '0000000112' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_5 = date . " tracking date for 5.

when '0000000113' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_10 = date . "tracking date for 10

when '0000000114' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_15 = date . "tracking date for 15

when '0000000115' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_20 = date . " tracking date for 20

when '0000000116' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_25 = date . " tracking date for 25

when '0000000117' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_30 = date . "tracking date for 30

when '0000000118' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_35 = date . "tracking date for 35

when '0000000119' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_40 = date . " tracking date for 40

when '0000000120' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_45 = date . " tracking date for 45

when '0000000121' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_50 = date . "tracking date for 50

when '0000000122' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_55 = date . "tracking date for 55

when '0000000123' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_60 = date . " tracking date for 60

when '0000000124' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_65 = date . " tracking date for 65

when '0000000125' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_70 = date . "tracking date for 70

when '0000000126' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_75 = date . "tracking date for 75

when '0000000127' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_78 = date . " tracking date for 78

when '0000000203' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_79 = date . " tracking date for 79

when '0000000128' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_80 = date . " tracking date for 80

when '0000000129' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_85 = date . "tracking date for 85

when '0000000130' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_90 = date . "tracking date for 90

when '0000000131' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dat_trk_95 = date . "tracking date for 95

when '0000000132' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dattrk_100 = date . " tracking date for100

when '0000000133' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dattrk_110 = date . " tracking date for110

when '0000000134' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dattrk_115 = date . "tracking date for 115

when '0000000135' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dattrk_120 = date . "tracking date for 120

when '0000000136' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-dattrk_105 = date . "tracking date for 105

*----

when '0000000137' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_1 = date . "plan trk date for 1

when '0000000138' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_5 = date . "plan trk date for 5

when '0000000139' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_10 = date . "plan trk date for 10

when '0000000140' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_15 = date . "plan trk date for 15

when '0000000141' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_20 = date . "plan trk date for 20

when '0000000142' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_25 = date . "plan trk date for 25

when '0000000143' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_30 = date . "plan trk date for 30

when '0000000144' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_35 = date . "plan trk date for 35

when '0000000145' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_40 = date . "plan trk date for 40

when '0000000146' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_45 = date . "plan trk date for 45

when '0000000147' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_50 = date . "plan trk date for 50

when '0000000148' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_55 = date . "plan trk date for 55

when '0000000149' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_60 = date . "plan trk date for 60

when '0000000150' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_65 = date . "plan trk date for 65

when '0000000151' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_70 = date . "plan trk date for 70

when '0000000152' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_75 = date . "plan trk date for 75

when '0000000153' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_78 = date . "plan trk date for 78

when '0000000202' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_79 = date . "plan trk date for 79

when '0000000154' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_80 = date . "plan trk date for 80

when '0000000155' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_85 = date . "plan trk date for 85

when '0000000156' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_90 = date . "plan trk date for 90

when '0000000157' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_95 = date . "plan trk date for 95

when '0000000158' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_100 = date . "plan trk date for 100

when '0000000159' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_105 = date . "plan trk date for 105

when '0000000160' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_110 = date . "plan trk date for 110

when '0000000161' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_115 = date . "plan trk date for 115

when '0000000162' .

perform date_convert using ausp_itab-atflv changing date .

zsdtab1_itab-pdt_tk_120 = date . "plan trk date for 120

********Additional fields / 24.05.98**********************************

when '0000000099' .

case ausp_itab-atwrt .

when '540' .

zsdtab1_itab-roll_blind = 'X' .

when '482' .

zsdtab1_itab-ground_clr = 'X' .

when '551' .

zsdtab1_itab-anti_theft = 'X' .

when '882' .

zsdtab1_itab-anti_tow = 'X' .

when '656' .

zsdtab1_itab-alloy_whel = 'X' .

when '265' .

zsdtab1_itab-del_class = 'X' .

when '280' .

zsdtab1_itab-str_wheel = 'X' .

when 'CDC' .

zsdtab1_itab-cd_changer = 'X' .

when '205' .

zsdtab1_itab-manual_eng = 'X' .

when '273' .

zsdtab1_itab-conn_handy = 'X' .

when '343' .

zsdtab1_itab-aircleaner = 'X' .

when '481' .

zsdtab1_itab-metal_sump = 'X' .

when '533' .

zsdtab1_itab-speaker = 'X' .

when '570' .

zsdtab1_itab-arm_rest = 'X' .

when '580' .

zsdtab1_itab-aircond = 'X' .

when '611' .

zsdtab1_itab-exit_light = 'X' .

when '613' .

zsdtab1_itab-headlamp = 'X' .

when '877' .

zsdtab1_itab-readlamp = 'X' .

when '808' .

zsdtab1_itab-code_ckd = 'X' .

when '708' .

zsdtab1_itab-del_prt_lc = 'X' .

when '593' .

zsdtab1_itab-ins_glass = 'X' .

when '955' .

zsdtab1_itab-zelcl = 'Elegance' .

when '593' .

zsdtab1_itab-zelcl = 'Classic' .

endcase .

endloop .

*///////////////////////////////////////////////////////////////////////

*--

Update the sales data .--

perform get_sales_order using mara_itab-matnr .

perform get_cartype using mara_itab-matnr .

*----

append zsdtab1_itab .

endloop.

*----

<<<

loop at zsdtab1_itab .

if zsdtab1_itab-cartype <> 'W-203'

or zsdtab1_itab-cartype <> 'W-210'

or zsdtab1_itab-cartype <> 'W-211'.

clear zsdtab1_itab-zelcl.

endif.

SELECT SINGLE * FROM ZSDTAB1 WHERE COMMNO = MARA_ITAB-MATNR .

select single * from zsdtab1 where commno = zsdtab1_itab-commno.

if sy-subrc <> 0 .

insert into zsdtab1 values zsdtab1_itab .

else .

update zsdtab1 set :vbeln = zsdtab1_itab-vbeln

bill_doc = zsdtab1_itab-bill_doc

dest = zsdtab1_itab-dest

lgort = zsdtab1_itab-lgort

ship_tp = zsdtab1_itab-ship_tp

country = zsdtab1_itab-country

kunnr = zsdtab1_itab-kunnr

vkbur = zsdtab1_itab-vkbur

customer = zsdtab1_itab-customer

city = zsdtab1_itab-city

region = zsdtab1_itab-region

model = zsdtab1_itab-model

drive = zsdtab1_itab-drive

converter = zsdtab1_itab-converter

transmssn = zsdtab1_itab-transmssn

colour = zsdtab1_itab-colour

ztrim = zsdtab1_itab-ztrim

commno = zsdtab1_itab-commno

trackstage = zsdtab1_itab-trackstage

chassis_no = zsdtab1_itab-chassis_no

engine_no = zsdtab1_itab-engine_no

body_no = zsdtab1_itab-body_no

cockpit = zsdtab1_itab-cockpit

airbag = zsdtab1_itab-airbag

trailer_no = zsdtab1_itab-trailer_no

fininspdat = zsdtab1_itab-fininspdat

entrydate = zsdtab1_itab-entrydate

regist_no = zsdtab1_itab-regist_no

mech_key = zsdtab1_itab-mech_key

side_ab_rt = zsdtab1_itab-side_ab_rt

side_ab_lt = zsdtab1_itab-side_ab_lt

elect_key = zsdtab1_itab-elect_key

head_lamp = zsdtab1_itab-head_lamp

tail_lamp = zsdtab1_itab-tail_lamp

vac_pump = zsdtab1_itab-vac_pump

sd_ab_sn_l = zsdtab1_itab-sd_ab_sn_l

sd_ab_sn_r = zsdtab1_itab-sd_ab_sn_r

asrhydunit = zsdtab1_itab-asrhydunit

gearboxno = zsdtab1_itab-gearboxno

battery = zsdtab1_itab-battery

tyretype = zsdtab1_itab-tyretype

tyremake = zsdtab1_itab-tyremake

tyresize = zsdtab1_itab-tyresize

rr_axle_no = zsdtab1_itab-rr_axle_no

ff_axl_nor = zsdtab1_itab-ff_axl_nor

ff_axl_nol = zsdtab1_itab-ff_axl_nol

drivairbag = zsdtab1_itab-drivairbag

st_box_no = zsdtab1_itab-st_box_no

transport = zsdtab1_itab-transport

*----

OPTIONS-

roll_blind = zsdtab1_itab-roll_blind

ground_clr = zsdtab1_itab-ground_clr

anti_theft = zsdtab1_itab-anti_theft

anti_tow = zsdtab1_itab-anti_tow

alloy_whel = zsdtab1_itab-alloy_whel

del_class = zsdtab1_itab-del_class

str_wheel = zsdtab1_itab-str_wheel

cd_changer = zsdtab1_itab-cd_changer

manual_eng = zsdtab1_itab-manual_eng

conn_handy = zsdtab1_itab-conn_handy

aircleaner = zsdtab1_itab-aircleaner

metal_sump = zsdtab1_itab-metal_sump

speaker = zsdtab1_itab-speaker

arm_rest = zsdtab1_itab-arm_rest

aircond = zsdtab1_itab-aircond

exit_light = zsdtab1_itab-exit_light

headlamp = zsdtab1_itab-headlamp

readlamp = zsdtab1_itab-readlamp

code_ckd = zsdtab1_itab-code_ckd

del_prt_lc = zsdtab1_itab-del_prt_lc

ins_glass = zsdtab1_itab-ins_glass

*----

dat_trk_1 = zsdtab1_itab-dat_trk_1

dat_trk_5 = zsdtab1_itab-dat_trk_5

dat_trk_10 = zsdtab1_itab-dat_trk_10

dat_trk_15 = zsdtab1_itab-dat_trk_15

dat_trk_20 = zsdtab1_itab-dat_trk_20

dat_trk_25 = zsdtab1_itab-dat_trk_25

dat_trk_30 = zsdtab1_itab-dat_trk_30

dat_trk_35 = zsdtab1_itab-dat_trk_35

dat_trk_40 = zsdtab1_itab-dat_trk_40

dat_trk_45 = zsdtab1_itab-dat_trk_45

dat_trk_50 = zsdtab1_itab-dat_trk_50

dat_trk_55 = zsdtab1_itab-dat_trk_55

dat_trk_60 = zsdtab1_itab-dat_trk_60

dat_trk_65 = zsdtab1_itab-dat_trk_65

dat_trk_70 = zsdtab1_itab-dat_trk_70

dat_trk_75 = zsdtab1_itab-dat_trk_75

dat_trk_78 = zsdtab1_itab-dat_trk_78

dat_trk_79 = zsdtab1_itab-dat_trk_79

dat_trk_80 = zsdtab1_itab-dat_trk_80

dat_trk_85 = zsdtab1_itab-dat_trk_85

dat_trk_90 = zsdtab1_itab-dat_trk_90

dat_trk_95 = zsdtab1_itab-dat_trk_95

dattrk_100 = zsdtab1_itab-dattrk_100

dattrk_105 = zsdtab1_itab-dattrk_105

dattrk_110 = zsdtab1_itab-dattrk_110

dattrk_115 = zsdtab1_itab-dattrk_115

dattrk_120 = zsdtab1_itab-dattrk_120

*----

pdt_tk_1 = zsdtab1_itab-pdt_tk_1

pdt_tk_5 = zsdtab1_itab-pdt_tk_5

pdt_tk_10 = zsdtab1_itab-pdt_tk_10

pdt_tk_15 = zsdtab1_itab-pdt_tk_15

pdt_tk_20 = zsdtab1_itab-pdt_tk_20

pdt_tk_25 = zsdtab1_itab-pdt_tk_25

pdt_tk_30 = zsdtab1_itab-pdt_tk_30

pdt_tk_35 = zsdtab1_itab-pdt_tk_35

pdt_tk_40 = zsdtab1_itab-pdt_tk_40

pdt_tk_45 = zsdtab1_itab-pdt_tk_45

pdt_tk_50 = zsdtab1_itab-pdt_tk_50

pdt_tk_55 = zsdtab1_itab-pdt_tk_55

pdt_tk_60 = zsdtab1_itab-pdt_tk_60

pdt_tk_65 = zsdtab1_itab-pdt_tk_65

pdt_tk_70 = zsdtab1_itab-pdt_tk_70

pdt_tk_75 = zsdtab1_itab-pdt_tk_75

pdt_tk_78 = zsdtab1_itab-pdt_tk_78

pdt_tk_79 = zsdtab1_itab-pdt_tk_79

pdt_tk_80 = zsdtab1_itab-pdt_tk_80

pdt_tk_85 = zsdtab1_itab-pdt_tk_85

pdt_tk_90 = zsdtab1_itab-pdt_tk_90

pdt_tk_95 = zsdtab1_itab-pdt_tk_95

pdt_tk_100 = zsdtab1_itab-pdt_tk_100

pdt_tk_105 = zsdtab1_itab-pdt_tk_105

pdt_tk_110 = zsdtab1_itab-pdt_tk_110

pdt_tk_115 = zsdtab1_itab-pdt_tk_115

pdt_tk_120 = zsdtab1_itab-pdt_tk_120

*----

cartype = zsdtab1_itab-cartype

zelcl = zsdtab1_itab-zelcl

excise_no = zsdtab1_itab-excise_no

where commno = zsdtab1_itab-commno .

Update table .---------<<<

endif .

endloop .

perform update_excise_date .

perform update_post_goods_issue_date .

perform update_time.

*///////////////////// end of programe /////////////////////////////////

*----

Get sales data -

form get_sales_order using matnr .

data : corr_vbeln like vbrk-vbeln .

ADDED BY ADITYA / 22.06.98 **************************************

perform get_order using matnr .

select single vbeln lgort into (zsdtab1_itab-vbeln , zsdtab1_itab-lgort)

from vbap where matnr = matnr . " C-22.06.98

from vbap where vbeln = zsdtab1_itab-vbeln .

if sy-subrc = 0 .

************Get the Excise No from Allocation Field*******************

select single * from zsdtab1 where commno = matnr .

if zsdtab1-excise_no = '' .

select * from vbrp where matnr = matnr .

select single vbeln into corr_vbeln from vbrk where

vbeln = vbrp-vbeln and vbtyp = 'M'.

if sy-subrc eq 0.

select single * from vbrk where vbtyp = 'N'

and sfakn = corr_vbeln. "cancelled doc.

if sy-subrc ne 0.

select single * from vbrk where vbeln = corr_vbeln.

if sy-subrc eq 0.

data : year(4) .

move sy-datum+0(4) to year .

select single * from bkpf where awtyp = 'VBRK' and awkey = vbrk-vbeln

and bukrs = 'MBIL' and gjahr = year .

if sy-subrc = 0 .

select single * from bseg where bukrs = 'MBIL' and belnr = bkpf-belnr

and gjahr = year and koart = 'D' and

shkzg = 'S' .

zsdtab1_itab-excise_no = bseg-zuonr .

endif .

endif.

endselect.

endif .

select single kunnr vkbur into (zsdtab1_itab-kunnr ,

zsdtab1_itab-vkbur) from vbak

where vbeln = zsdtab1_itab-vbeln .

if sy-subrc = 0 .

select single name1 ort01 regio into (zsdtab1_itab-customer ,

zsdtab1_itab-city , zsdtab1_itab-region) from kna1

where kunnr = zsdtab1_itab-kunnr .

endif.

Get Ship to Party **************************************************

select single * from vbpa where vbeln = zsdtab1_itab-vbeln and

parvw = 'WE' .

if sy-subrc = 0 .

zsdtab1_itab-ship_tp = vbpa-kunnr .

Get Destination Country of Ship to Party .************

select single * from kna1 where kunnr = vbpa-kunnr .

if sy-subrc = 0 .

select single * from t005t where land1 = kna1-land1

and spras = 'E' .

if sy-subrc = 0 .

zsdtab1_itab-country = t005t-landx .

endif .

endform. " GET_SALES

*----

form update_time.

update zstatus set zupddate = sy-datum

uzeit = sy-uzeit

where programm = 'ZSDDET01' .

endform. " UPDATE_TIME

&----

*& Form DATE_CONVERT

&----

form date_convert using atflv changing date .

data : dt(8) , dat type i .

dat = atflv .

dt = dat .

date = dt .

endform. " DATE_CONVERT

*///////////////////////////////////////////////////////////////////////

&----

*& Form UPDATE_POST_GOODS_ISSUE_DATE

----

form update_post_goods_issue_date .

types : begin of itab1_type ,

mblnr like mseg-mblnr ,

budat like mkpf-budat ,

end of itab1_type .

data : itab1 type itab1_type occurs 10 with header line .

loop at mara_itab .

select single * from zsdtab1 where commno = mara_itab-matnr .

if sy-subrc = 0 and zsdtab1-postdate = '00000000' .

refresh itab1 . clear itab1 .

select * from mseg where matnr = mara_itab-matnr and bwart = '601' .

itab1-mblnr = mseg-mblnr .

append itab1 .

endselect .

loop at itab1 .

select single * from mkpf where mblnr = itab1-mblnr .

if sy-subrc = 0 .

itab1-budat = mkpf-budat .

modify itab1 .

endif .

endloop .

sort itab1 by budat .

read table itab1 index 1 .

if sy-subrc = 0 .

update zsdtab1 set postdate = itab1-budat

where commno = mara_itab-matnr .

endif .

endloop .

endform. " UPDATE_POST_GOODS_ISSUE_DATE

&----

*& Form UPDATE_EXCISE_DATE

----

form update_excise_date.

types : begin of itab2_type ,

mblnr like mseg-mblnr ,

budat like mkpf-budat ,

end of itab2_type .

data : itab2 type itab2_type occurs 10 with header line .

loop at mara_itab .

select single * from zsdtab1 where commno = mara_itab-matnr .

if sy-subrc = 0 and zsdtab1-excise_dat = '00000000' .

refresh itab2 . clear itab2 .

select * from mseg where matnr = mara_itab-matnr and

( bwart = '601' or bwart = '311' ) .

itab2-mblnr = mseg-mblnr .

append itab2 .

endselect .

loop at itab2 .

select single * from mkpf where mblnr = itab2-mblnr .

if sy-subrc = 0 .

itab2-budat = mkpf-budat .

modify itab2 .

endif .

endloop .

sort itab2 by budat .

read table itab2 index 1 .

if sy-subrc = 0 .

update zsdtab1 set excise_dat = itab2-budat

where commno = mara_itab-matnr .

endif .

endloop .

endform. " UPDATE_EXCISE_DATE

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

form get_order using matnr .

types : begin of itab_type ,

vbeln like vbap-vbeln ,

posnr like vbap-posnr ,

end of itab_type .

data : itab type itab_type occurs 10 with header line .

refresh itab . clear itab .

select * from vbap where matnr = mara_itab-matnr .

itab-vbeln = vbap-vbeln .

itab-posnr = vbap-posnr .

append itab .

endselect .

loop at itab .

select single * from vbak where vbeln = itab-vbeln .

if vbak-vbtyp <> 'C' .

delete itab .

endif .

endloop .

loop at itab .

select single * from vbfa where vbelv = itab-vbeln and

posnv = itab-posnr and vbtyp_n = 'H' .

if sy-subrc = 0 .

delete itab .

endif .

endloop .

clear : zsdtab1_itab-vbeln , zsdtab1_itab-bill_doc .

loop at itab .

zsdtab1_itab-vbeln = itab-vbeln .

select single * from vbfa where vbelv = itab-vbeln and

posnv = itab-posnr and vbtyp_n = 'M' .

if sy-subrc = 0 .

zsdtab1_itab-bill_doc = vbfa-vbeln .

endif .

endloop .

endform .

&----

*& Form GET_CARTYPE

----

form get_cartype using matnr .

select single * from mara where matnr = matnr .

zsdtab1_itab-cartype = mara-satnr .

endform. " GET_CARTYPE

Former Member · ‎10-07-2008

Do a Runtime Analysis Check using SE30 and find out which part of the program is taking more time to execute.

I could see a lot of select single statements inside loop-endloop in your code. Try to write a collective select statement out of the loops and get the data into an internal table .

The Tips and Tricks Button in SE30 will help you understand the performance optimization.

former_member194613 · ‎10-07-2008

you must run a SE30 Trace with it

... but 30hours can not be traced in high accurcy, because the largest number of the

time field is about 4.000.000.000 microsec.

Either reduce accuracy, milliseconds should be o.k.

or reduce load, for testing a smaller portion should work.

To find bottlenecks, you must know which tables will be large and which will not be large.

Looking at the coding alone in not very useful.

Siegfried

Former Member · ‎10-07-2008

Hi,

Avoid using nested select statements, use for all entries. Do not use internal table with header, instead use standard ITAB with work-area. Also check if all the tables used in Select are transparent table, cluster table takes more time to load. Also avoid using select *. Instead of using select single, use upto 1 row.

Regards,

Prashant.

Edited by: Prashant Kumar Upadhyay on Oct 7, 2008 12:20 PM

Former Member · ‎10-07-2008

Hi,

Use the Tcode SCI.

Brgds

Julien

Former Member · ‎10-07-2008

It took ten years to see there was a problem?

Look at this portion of the code:

select * from vbrp where matnr = matnr .

There is no index on MATNR in VBRP and I believe this is called in a loop.

Rob

ThomasZloch · ‎10-07-2008

Here is another similar one:

select * from vbap where matnr = mara_itab-matnr.

I'd say there is several major and many minor glitches in this code. It should be re-designed completely, but not via SDN...

Thomas

P.S. hint for you and others posting huge chunks of code...please put it inside tags!

Former Member · ‎10-08-2008

Thanks Rob,

I know including this statement and some other statements are also having issues. Can you tell me what can be a solution??

Former Member · ‎10-08-2008

Hi Thomas,

I agreed with you there is lot of problem in that code it should be re-designed completely but Clint wants to modify to the same program. What to do?

ThomasZloch · ‎10-08-2008

Do what was suggested already: run an SE30 runtime analysis with a small selection, sort by "net time" descending, and tackle the problems top down, worst one first. You can come back here with single ABAP statements, if you're unsure about how to fix them. But do search first, there is an abundance of existing solutions available.

Thomas

Former Member · ‎10-08-2008

Hi Boss,

A lot of performance issues is there ,

first You try to avoid Select *

second in the loop he written select statement , that is a big performance issue. You suppose to remove that.

Third is while he writing the Read statment he doesn't maintain

the BINARY SEARCH.

Do this first and let me know incase of any issues.

With Regards,

Narasimha Rao,

Former Member · ‎10-08-2008

Hi,

i checked your prg.i suggest two points

1.use INTO TABLE clause insteed of SELECT-ENDSELECT.

2.Most of the SELECT is not using INDEX. Try to use index in SELECT or create index for existing SELECT.

Former Member · ‎10-10-2008

Hi,

I have analysed your program and i would like to share following points for better performance of this report :

(a) Use the field Names instead of Select * or Select Single * as if you use the field names it will consume less amount of resources inside the loop as well as you have lot many Select Single * and u r using very big tables like VBAP and many more.

(b) Trace on ST05 which particular query is mostly effecting your system or use ST12 in current mode to trace for less inputs which run the report for 20-30 min so that we get an idea which queries are effecting the system and taking a lot of time.

(c) In Case of internal tables sort the data properly and use binary search for getting the data.

I think this will help.

Thanks and Regards,

Harsh