Hi,
I have performance issue on this report, Please let me know if the code can be written in a better way, I am following the indices and order everything,
Code:
select bukrs hkont augdt augbl gjahr belnr buzei
budat bldat xblnr blart shkzg dmbtr wrbtr
sgtxt aufnr kostl dmbe2 prctr zztitle
zzterr from bsis into table i_data
WHERE hkont IN so_hkont
AND bukrs IN so_bukrs
AND augdt IN so_augdt
AND gjahr IN so_gjahr
AND belnr IN so_belnr
AND budat IN so_budat
AND bldat IN so_bldat
AND xblnr IN so_xblnr
AND blart IN so_blart
AND aufnr IN so_aufnr
AND kostl IN so_kostl
AND prctr IN so_prctr
AND zztitle IN so_title
AND zzterr IN so_terr.
if not i_data[] is initial.
move i_data[] to t_data[].
loop at t_data.
select single cpudt bvorg waers bstat hwaer hwae2
from bkpf into corresponding fields of t_data
where bukrs = t_data-bukrs and
belnr = t_data-belnr and
gjahr = t_data-gjahr.
modify t_data index sy-tabix.
endloop.
if not so_cpudt is initial.
if so_cpudt-high is initial.
move so_cpudt-low to so_cpudt-high.
endif.
so_cpudt-sign = 'E'.
modify so_cpudt index 1.
delete t_data where cpudt in so_cpudt.
endif.
if not so_bvorg is initial.
if so_bvorg-high is initial.
move so_bvorg-low to so_bvorg-high.
endif.
so_bvorg-sign = 'E'.
modify so_bvorg index 1.
delete t_data where bvorg in so_bvorg.
endif.
refresh i_data.
clear i_data.
clear t_data.
free i_data.
This was the earlier code, code had inner join,
I changed to above manner
Older version
Code:
SELECT bsisbukrs bsisbelnr bsisgjahr bsisblart bsis~bldat
bsisbudat bsisxblnr bkpfbvorg bkpfbstat bkpf~waers
bkpfhwaer bkpfhwae2 bsisbuzei bsisaugdt bsis~augbl
bsisshkzg bsisdmbtr bsiswrbtr bsisdmbe2 bsis~sgtxt
bsiskostl bsisaufnr bsishkont bsisprctr bkpf~cpudt
bsiszztitle bsiszzterr
FROM bsis
JOIN bkpf
ON bkpfbukrs EQ bsisbukrs
AND bkpfbelnr EQ bsisbelnr
AND bkpfgjahr EQ bsisgjahr
INTO CORRESPONDING FIELDS OF t_data
WHERE bsis~bukrs IN so_bukrs
AND bsis~hkont IN so_hkont
AND bsis~budat IN so_budat
AND bsis~augdt IN so_augdt
AND bsis~gjahr IN so_gjahr
AND bsis~belnr IN so_belnr
AND bsis~blart IN so_blart
AND bsis~xblnr IN so_xblnr
AND bsis~bldat IN so_bldat
AND bsis~zztitle IN so_title
AND bsis~zzterr IN so_terr
AND bsis~kostl IN so_kostl
AND bsis~prctr IN so_prctr
AND bsis~aufnr IN so_aufnr
AND bkpf~cpudt IN so_cpudt "-( BKPF
AND bkpf~bvorg IN so_bvorg. "- (BKPF)
For some selection criteria, the older version is executing fast than new version, but most of the times the new version of code which I changed is executed fast.
please let me know if any changes I need to make in the new version of code to make it better so that for any selection criteria it is executed first.
Thanks
Thanks
4 Answers
-
Former MemberPosted on Oct 19, 2006 at 06:59 PMA couple of questions:
1) I do not see why you need 2 tables i_data & t_data in your code as it looks like they have the same structure.
2) I would use a bit different logic to populate fields from bkpf. in your logic for the accounting document with 100 items you will select BKPF 100 times which is not good.
I would rather use something like (after your select from BSIS):
if not i_data[] is initial.
sort i_data by bukrs gjahr belnr.
loop at i_data.
if bkpf-bukrs ne i_data-bukrs or
bkpf-gjahr ne i_data-gjahr or
bkpf-belnr ne i_data-belnr.
t_data = i_data. "header-line only !!!
select single cpudt bvorg waers bstat hwaer hwae2
from bkpf into corresponding fields of t_data
where bukrs = i_data-bukrs
and belnr = i_data-belnr
and gjahr = i_data-gjahr.
endif.
i_data = t_data.
modify i_data index sy-tabix.
endloop.
This way you only select bkpf once per document.
Another option is to write 2 selects based on the selection criteria you have like for example - if you have a lis tof bukrs & so_cpudt set to 1 day -> I think it will be much faster to select from bkpf first as it has index on BUKRS/CPUDT, then select from BSIS. If you have just a couple of accounts and wide date range -> then it's faster to select form BSIS first. So, I would suggest you think about "adaptive" selection logic based on your current selection criteria.
Add a comment
-
Former MemberPosted on Oct 19, 2006 at 09:40 PM
Hi Chary,
You coded:
loop at t_data.
select single cpudt bvorg waers bstat hwaer hwae2
from bkpf into corresponding fields of t_data
where bukrs = t_data-bukrs and
belnr = t_data-belnr and
gjahr = t_data-gjahr.
modify t_data index sy-tabix.
endloop.
You are using select inside a loop which is not good from performance point of view and should be replaced by FOR ALL ENTRIES
You can change the code :
data: i_data1 like i_data occurs 0 with header line,
i_data2 like i_data occurs 0 with header line.
if not i_data[] is initial.
i_data2[] = i_data[].
sort i_data2 by bukrs belnr gjahr.
delete adjacent duplicates from i_data2 comparing bukrs belnr gjahr.
select bukrs belnr gjahr cpudt bvorg waers bstat hwaer hwae2
from bkpf into corresponding fields of i_data1
for all entries in i_data2
where bukrs = i_data2-bukrs and
belnr = i_data2-belnr and
gjahr = i_data2-gjahr.
if sy-subrc eq 0.
sort i_data1 by bukrs belnr gjahr.
loop at i_data.
clear i_data1.
read table i_data1 with key bukrs = i_data-bukrs
belnr = i_data-belnr
gjahr = i_data-gjahr
binary search.
if sy-subrc eq 0.
move-corresponding i_data1 to i_data.
modify i_data.
endif.
endloop.
endif.
endif.
Also you are using statement like
if not so_cpudt is initial.
IT seems to be ranges or selection screen parameter.
It should be replaced by
if not so_cpudt[] is initial.
similar for
if not so_bvorg is initial.
Add a comment
-
Former MemberPosted on Oct 20, 2006 at 02:51 PM
HI,
HERE U HAVE TWO SELECTS AND INE SELECT IS OUTSIDE AND ONE MORE IS WITH IN THE LOOP.
SECOND ONE MAY CAUSE TO PEFORMANCE ISSUE SO THATS Y WN U SELECT THE VALUES BASED ON THE FIRST INTERNAL TABLE PLEASE USE FOR ALL ENTRIES IN FIRST TABLE
EX
1)
SELECT -
INTO IT_ITAB
IF NOT IT_ITAB[] IS INTITIAL.
SELECT -
INTO IT_JTAB
FOR ALL ENTRIES IN IT_ITAB.
I THINK THIS MAY WORK
2) IF UR MAINTAINING IT IN ORDER Y DO U HAVE USED CORRESPONDING INTO FIELDS OF THIS IS ALSO ONE REASONE WHICH SLOW DOWNS.
THANKS AND REGARDS
MAHESH.G
Add a comment
Add a comment