2008 Oct 14 7:36 AM
Hi,
I am getting Timeout error while appending the itab3 into itab4.
Below is the code which iam facing the problem.
data: l_flg.
refresh: itab3.
select vbak~ernam vbak~vbeln vbap~posnr
vbap~matnr vbap~arktx vbap~kwmeng
vbap~erdat vbap~prodh
vbap~netwr vbap~wavwr vbak~knumv
vbak~auart vbak~vkbur
vbap~knuma_pi vbap~knuma_ag
into corresponding fields of table itab3
from vbak inner join vbap
on vbak~vbeln = vbap~vbeln
where vbak~erdat in s_date
and vbak~auart in s_type
and vbap~prodh in s_hier
and vbap~ernam in s_name
and vbak~kunnr in s_soldto
and vbak~vkorg in s_org
and vbak~vtweg in s_dist
and vbak~spart in s_div
and vbak~vkbur in s_slsoff
and vbap~knuma_pi in s_slspmo
and vbap~knuma_ag in s_slsdl.
if not itab3[] is initial.
select knumv
kposn
kappl
kschl
kwert from konv
into table tab_kwert
for all entries in itab3
where konv~knumv = itab3-knumv
and konv~kposn = itab3-posnr
and konv~kappl = 'V'
and konv~kschl in s_ctype.
sort tab_kwert by knumv kposn.
endif.
clear itab4.
refresh itab4.
loop at itab3 assigning <fs_tab3>.
clear: l_flg.
loop at tab_kwert where knumv = <fs_tab3>-knumv
and kposn = <fs_tab3>-posnr
and kappl = 'V'.
l_flg = 'X'.
<fs_tab3>-kschl = tab_kwert-kschl.
<fs_tab3>-kwert = tab_kwert-kwert.
select single * from vbkd where vbeln eq <fs_tab3>-vbeln.
if sy-subrc = 0.
<fs_tab3>-prsdt = vbkd-prsdt.
endif.
itab4 = itab3.
append itab4.
clear itab4.
commit work.
endloop.
if l_flg is initial.
delete itab3.
endif.
endloop.
Provide me some tips to avoid this problem
Thanks
2008 Oct 14 7:20 PM
2008 Oct 14 7:39 AM
Hi,
How many number of records are there in internal table "itab3" and "tab_kwert" , if there is huge number of records then this issue will come. In order to avoid this dump, ur BASIS team has to set some parameter, to increase the time out time.
Thanks & Regards,
Navneeth K.
Edited by: Navneeth Bothra on Oct 14, 2008 8:39 AM
2008 Oct 14 7:46 AM
Navneeth,
Thanks for your reply there are 30,000+ records are there in table tab_kwert.
Is there any changes need to maintain in code to increase the performance?
Thanks
2008 Oct 14 7:50 AM
hey,
you are selecting the data form vbak and vbap which the header and the iten table.Why cant you select the data from vabak first into an internal table and after that using the intenal table with for all enteries od the second selection fron vbap.You are havig vbeln in common and you can map the fields which are in common this will definitly increase your performance.
Regards,
Midhun Abraham
Edited by: Midhun Abraham on Oct 14, 2008 8:51 AM
2008 Oct 14 7:44 AM
Hi may be your program is going in some infinite loop,
if l_flg is initial.
delete itab3. "check if it executing with sy-subrc eq 0.
endif.
2008 Oct 14 7:57 AM
Navneeth,
Before that condition only i am facing the timed out problem.
loop at itab3 assigning <fs_tab3>.
clear: l_flg.
loop at tab_kwert where knumv = <fs_tab3>-knumv
and kposn = <fs_tab3>-posnr
and kappl = 'V'.
l_flg = 'X'.
<fs_tab3>-kschl = tab_kwert-kschl.
<fs_tab3>-kwert = tab_kwert-kwert.
select single * from vbkd where vbeln eq <fs_tab3>-vbeln.
if sy-subrc = 0.
<fs_tab3>-prsdt = vbkd-prsdt.
endif.
itab4 = itab3.
append itab4.
clear itab4.
commit work.
endloop.
if l_flg is initial.
delete itab3.
endif.
endloop.
is there any possibility to avoid the problem
Thanks
2008 Oct 14 7:45 AM
2008 Oct 14 7:47 AM
Hi,
Using the CORRESPONDING FIELDS addition will bring down the performance extensively. Try to avoid it.
Also ask your basis consultant to change the System parameter rdisp/max_wprun_time.
Regards
2008 Oct 14 7:52 AM
Avoid the nested loop of itab3 and tab_kwert, internal table tab_kwert will contain only for those entries which are there in itab3 (since you are using a for all entries).
I will try to modify the code and paste it here.
Key: Avoid nested loops.
2008 Oct 14 7:56 AM
One more major thing please remove :
select single * from vbkd where vbeln eq <fs_tab3>-vbeln
from the loop. Before the loop starts use for all entries and populate a internal table and do read operation to fetch entry.
Try to change this first, rather than changing nested loops.
If this too doesnt work out we can go for removing nested loops.
2008 Oct 14 7:59 AM
2008 Oct 14 8:06 AM
data: l_flg.
refresh: itab3.
select vbak~ernam vbak~vbeln vbap~posnr
vbap~matnr vbap~arktx vbap~kwmeng
vbap~erdat vbap~prodh
vbap~netwr vbap~wavwr vbak~knumv
vbak~auart vbak~vkbur
vbap~knuma_pi vbap~knuma_ag
into corresponding fields of table itab3
from vbak inner join vbap
on vbak~vbeln = vbap~vbeln
where vbak~erdat in s_date
and vbak~auart in s_type
and vbap~prodh in s_hier
and vbap~ernam in s_name
and vbak~kunnr in s_soldto
and vbak~vkorg in s_org
and vbak~vtweg in s_dist
and vbak~spart in s_div
and vbak~vkbur in s_slsoff
and vbap~knuma_pi in s_slspmo
and vbap~knuma_ag in s_slsdl.
if not itab3[] is initial.
select knumv
kposn
kappl
kschl
kwert from konv
into table tab_kwert
for all entries in itab3
where konv~knumv = itab3-knumv
and konv~kposn = itab3-posnr
and konv~kappl = 'V'
and konv~kschl in s_ctype.
sort tab_kwert by knumv kposn.
endif.
clear itab4.
refresh itab4.
loop at tab_kwert where kappl = 'V'.
read table itab3 assigning <fs_tab3> with key knumv = <fs_tab3>-knumv
kposn = <fs_tab3>-posnr.
if sy-subrc eq 0.
<fs_tab3>-kschl = tab_kwert-kschl.
<fs_tab3>-kwert = tab_kwert-kwert.
select single * from vbkd where vbeln eq <fs_tab3>-vbeln.
if sy-subrc = 0.
<fs_tab3>-prsdt = vbkd-prsdt.
endif.
itab4 = itab3.
append itab4.
clear itab4.
endloop.
2008 Oct 14 8:20 AM
Hi Swetha,
Is the modified piece of code fine ?
Thanks & Regards,
Navneeth K.
Edited by: Navneeth Bothra on Oct 14, 2008 11:27 AM
2008 Oct 14 3:36 PM
Navneeth,
Still iam facing the same problem(Timelimt exceed).My itab3 has 55000 records and tab_kwert having 320,000 records. here the part of the code
sort itab3 by vbeln posnr matnr.
loop at tab_kwert where kappl = 'V'.
read table itab3 assigning <fs_tab3> with key knumv = tab_kwert-knumv
posnr = tab_kwert-kposn.
if sy-subrc eq 0.
l_flg = 'X'.
<fs_tab3>-kschl = tab_kwert-kschl.
<fs_tab3>-kwert = tab_kwert-kwert.
select single * from vbkd where vbeln eq <fs_tab3>-vbeln.
if sy-subrc = 0.
<fs_tab3>-prsdt = vbkd-prsdt.
endif.
itab4 = <fs_tab3>.
append itab4.
clear itab4.
endif.
endloop.
Provide me some more light on this.
Thanks
2008 Oct 14 4:02 PM
What is the structure of itab3?
Can you use the BINARY SEARCH addition of READ TABLE?
Otherwise you're doing about 8 billion reads (320,000 * 55,000 / 2). If knumv & posnr are the starting fields of itab3 the BINARY SEARCH addition will speed things up (about 3000 times).
If TAB has n entries, linear search runs in O( n ) time, whereas binary search takes only O( log2( n ) ).
Edited by: Michael Evershed on Oct 14, 2008 5:04 PM
2008 Oct 14 4:52 PM
Michal,
The strcture of itab3 is.
DATA: BEGIN OF itab3 OCCURS 0,
ernam LIKE vbap-ernam, "USER
vbeln LIKE vbap-vbeln, "SALES DOC. #
posnr LIKE vbap-posnr, "LINE #
matnr LIKE vbap-matnr, "ITEM NUMBER
arktx(30) TYPE c, "ITEM DESCRIPTION
kwmeng TYPE p, "ORDER QUANTITY
erdat LIKE vbap-erdat, "CREATE DATE
prsdt LIKE vbkd-prsdt, "PRICING DATE
prodh LIKE vbap-prodh, "PRODUCT HEIRACHY
netwr LIKE vbap-netwr, "NET VALUE
wavwr LIKE vbap-wavwr, "STD/FOB COST
knumv LIKE vbak-knumv, "CONDITION #
auart LIKE vbak-auart, "ORDER TYPE
kunnr LIKE vbak-kunnr, "SOLD TO
vkorg LIKE vbak-vkorg, "SALES ORD
vtweg LIKE vbak-vtweg, "DIST CHANNEL
spart LIKE vbak-spart, "DIVISION
pltyp LIKE vbkd-pltyp, "PRICE LIST CODE
konda LIKE vbkd-konda, "PRICE GROUP
kbetr LIKE konp-kbetr, "LIST PRICE
name1 LIKE kna1-name1, "CUSTOMER NAME
shname LIKE adrc-name1, "Ship To Name
pct1(6) TYPE c, "LIST/NET WORTH
pct2(6) TYPE c, "DLR NET/NET WORTH
pct3(6) TYPE c,
vkbur LIKE vbak-vkbur, "SALES OFFICE
knuma_pi LIKE vbap-knuma_pi,
knuma_ag LIKE vbap-knuma_ag,
waerk LIKE vbak-waerk, "CURRENCY
dlrnt LIKE vbap-netwr, "DEALER NET
kondm LIKE mvke-kondm, "MATERIAL PRICING CODE
kschl LIKE konv-kschl, "CONDITION TYPE
kwert like konv-kwert, "CONDITION VALUE
END OF itab3.
.
Thanks
2008 Oct 14 5:02 PM
Move knumv and posnr to the top of the ITAB3 structure and do a SORT itab3 BY knumv posnr just before the loop and add BINARY SEARCH to the READ TABLE line.
If you can't change ITAB3's structure create an ITAB4 with the same fields as ITAB3 but with knumv and posnr at the top and fill it with a loop of itab3. That READ TABLE command is going to kill you unless you can use a BINARY SEARCH.
2008 Oct 14 7:08 PM
Hi,
I have modified the code like below.However when i modify the ITAB3 Again it is giving Timelimt exceed error.
modify itab3 transporting check where knumv = tab_kwert-knumv
posnr = tab_kwert-kposn.
data: begin of itab_temp occurs 0,
vbeln like vbkd-vbeln,
prsdt like vbkd-prsdt,
end of itab_temp.
select vbeln prsdt into table itab_temp from vbkd
for all entries in itab3
where vbeln = itab3-vbeln.
sort tab_kwert by knumv kposn.
delete tab_kwert where kappl <> 'V'.
sort itab3 by knumv posnr.
loop at tab_kwert.
read table itab3 with key knumv = tab_kwert-knumv
posnr = tab_kwert-kposn
binary search.
if sy-subrc = 0.
read table itab_temp with key vbeln = itab3-vbeln binary search.
if sy-subrc = 0.
itab3-prsdt = itab_temp-prsdt.
endif.
itab3-kschl = tab_kwert-kschl.
itab3-kwert = tab_kwert-kwert.
itab4 = itab3.
append itab4.
clear itab4.
itab3-check = 'X'.
modify itab3 transporting check where knumv = tab_kwert-knumv
posnr = tab_kwert-kposn.
endif.
endloop.
sort itab3 by check.
delete itab3 where check <> 'X'.
.
Provide me some light on the issue.
Thanks
2008 Oct 14 7:20 PM
2008 Oct 14 7:26 PM
Rob,
I have avoided nested loops instead of i have read the table inside the loop.but I am facing problem while modify the itab3 table.here it is giving time limit exceed error.
Thanks
2008 Oct 14 7:36 PM
No - your modify is using an implicit loop (MODIFY ... WHERE).
Michael's answer looks pretty good.
Rob
Edited by: Rob Burbank on Oct 14, 2008 2:37 PM
2008 Oct 14 8:58 PM
Rob/Michal,
It is working fine for 1 to 2 months data.But when i excute with more than 2 months it is giving the Timelimit error.
Thanks
2008 Oct 14 9:05 PM
I don't see a SORT for itab_temp so the READ may not be taking advantage of the BINARY SEARCH.
Could the problem be in another section of code? I don't see much we can do to speed up this part.
2008 Oct 14 9:09 PM
Try running it in the background.
And do a runtime analysis (SE30) to pinpoint the problem.
Rob
Edited by: Rob Burbank on Oct 14, 2008 4:11 PM
2008 Oct 14 9:14 PM
>
> I don't see a SORT for itab_temp so the READ may not be taking advantage of the BINARY SEARCH.
Michael - if the (standard) tables aren't sorted, then the BINARY SEARCH will produce a return code if not found. It won't turn around and do a sequential search.
Rob
2008 Oct 14 7:29 PM
OK, try the changes in red.
data: begin of itab_temp occurs 0,
vbeln like vbkd-vbeln,
prsdt like vbkd-prsdt,
end of itab_temp.
data: w3_idx like sy-tabix.
select vbeln prsdt into table itab_temp from vbkd
for all entries in itab3
where vbeln = itab3-vbeln.
sort tab_kwert by knumv kposn.
delete tab_kwert where kappl 'V'.
sort itab3 by knumv posnr.
loop at tab_kwert.
read table itab3 with key knumv = tab_kwert-knumv
posnr = tab_kwert-kposn
binary search.
if sy-subrc = 0.
w3_idx = sy-tabix.
read table itab_temp with key vbeln = itab3-vbeln binary search.
if sy-subrc = 0.
itab3-prsdt = itab_temp-prsdt.
endif.
itab3-kschl = tab_kwert-kschl.
itab3-kwert = tab_kwert-kwert.
itab4 = itab3.
append itab4.
clear itab4.
itab3-check = 'X'.
modify itab3 transporting check index w3_idx.
endif.
endloop.
sort itab3 by check.
delete itab3 where check 'X'..
2008 Oct 15 4:47 AM
Hi Swetha,
Why dont you populate itab4 by a select statement, like you can do a inner join on vbak,vbap,konv.
Thanks & Regards,
Navneeth K.
2008 Oct 15 6:48 AM
Navneeth,
>>>Why dont you populate itab4 by a select statement, like you can do a inner join on vbak,vbap,konv.
could u explain me in brife?
Thanks
2008 Oct 15 7:52 AM
Hi swetha,
Pls.look into below idea which may help you.
sort itab3 by vbeln posnr matnr.
select single * from vbkd into table it_vbkd for all entries in itab3 where vbeln = itab3-vebeln.
use read command to read the records of it_vbkd inside the loop so that u wil not need to go to the data base table ( vdkd )each time loop runs . if u go to data base table each time loop runs , performance wil be bad.
Regards ,
Kiran