topic Re: Performance issue in Application Development and Automation Discussions

Performance issue

Former Member — Thu, 19 Oct 2006 15:51:06 GMT

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 bsis_{bukrs bsis}belnr bsis_{gjahr bsis}blart bsis~bldat

bsis_{budat bsis}xblnr bkpf_{bvorg bkpf}bstat bkpf~waers

bkpf_{hwaer bkpf}hwae2 bsis_{buzei bsis}augdt bsis~augbl

bsis_{shkzg bsis}dmbtr bsis_{wrbtr bsis}dmbe2 bsis~sgtxt

bsis_{kostl bsis}aufnr bsis_{hkont bsis}prctr bkpf~cpudt

bsis_{zztitle bsis}zzterr

FROM bsis

JOIN bkpf

ON bkpf_{bukrs EQ bsis}bukrs

AND bkpf_{belnr EQ bsis}belnr

AND bkpf_{gjahr EQ bsis}gjahr

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

Re: Performance issue

Former Member — Thu, 19 Oct 2006 16:50:30 GMT

The most important thing will be to make sure that so_hkont is not empty. there are anumber of conditions in your where clause. You might remove them and check them in the loop instead.

Rob

Re: Performance issue

Former Member — Thu, 19 Oct 2006 17:23:58 GMT

Hi Rob,

I am making sure that so_hkont is not empty, I am testing for all company codes.

I removed all the where conditions , kept only so_hkont and so_bukrs but still its taking time

Could you please give me an example for making better.

Thanks

Re: Performance issue

Former Member — Thu, 19 Oct 2006 17:45:28 GMT

OK - it takes a while, but how many records does the select return?

Rob

Re: Performance issue

Former Member — Thu, 19 Oct 2006 18:18:32 GMT

It is returning only 2000 records after all the search.

but its taking long time.

please let me know an example

thanks rob

Thanks

Re: Performance issue

Former Member — Thu, 19 Oct 2006 18:33:09 GMT

Well, that's not a lot of records. Could you please post the contents of SO_BUKRS and SO_HKONT where you get these records?

Rob

Re: Performance issue

Former Member — Thu, 19 Oct 2006 18:37:47 GMT

You also have to make sure that SO_BUKRS is not empty. If it is, I wrote ablog that will help:

/people/rob.burbank/blog/2006/09/13/using-an-index-when-you-dont-have-all-of-the-fields

Essentially, you only need to do this:


if so_bukrs[] is initial.
  so_bukrs-option = 'EQ'.
  so_bukrs-sign   = 'I'.
  SELECT bukrs FROM  t001
    INTO so_bukrs-low.
    APPEND so_bukrs.
  ENDSELECT.
endif.

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.

Rob

Message was edited by: Rob Burbank

Re: Performance issue

Former Member — Thu, 19 Oct 2006 18:47:56 GMT

so_budat: 04/01/2006 to 09/30/2006

so_augdt:

so_hkont: 50000 to 69999 "mandatory

so_bukrs: 000 to 9999 " mandatory

so_belnr:

so_gjahr: 2007 to 2007

so_bvorg:

so_cpudt:

so_bldat:

so_blart:

so_xblnr:

so_title: (bsis-zztitle)

so_terr : (bsis-zzterr)

so_kostl:

so_prctr:

so_aufnr:

so_matnr:

so_lifnr:

so_kunnr:

so_zzkun:

This is selection criteria I am using.

please let me know if you need more info.

Thanks

Re: Performance issue

Former Member — Thu, 19 Oct 2006 18:53:48 GMT

Try replacing the range in so_bukrs with the logic I gave earlier. I think this is your problem.

You might also want to go back and use the original join. That will help a bit as well.

Rob

Message was edited by: Rob Burbank

Re: Performance issue

Former Member — Thu, 19 Oct 2006 18:59:01 GMT

A 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.

Re: Performance issue

Former Member — Thu, 19 Oct 2006 20:36:14 GMT

Rob,

Try replacing the range in so_bukrs with the logic I gave earlier ?.

your logic is if so_bukrs is empty, but here its not empty,can you let me know what i have to do.

i did'tn get you..

Mahulenkta

i will try your suggestion also.

Thanks

Re: Performance issue

Former Member — Thu, 19 Oct 2006 20:49:17 GMT

mahulenka

the structures if i_data and t_data are not same,

it is like this,

data: begin of i_data occurs 0,

bukrs like bsis-bukrs,

hkont like bsis-hkont,

augdt like bsis-augdt,

augbl like bsis-augbl,

gjahr like bsis-gjahr,

belnr like bsis-belnr,

buzei like bsis-buzei,

budat like bsis-budat,

bldat like bsis-bldat,

xblnr like bsis-xblnr,

blart like bsis-blart,

shkzg like bsis-shkzg,

dmbtr like bsis-dmbtr,

wrbtr like bsis-wrbtr,

sgtxt like bsis-sgtxt,

aufnr like bsis-aufnr,

kostl like bsis-kostl,

dmbe2 like bsis-dmbe2,

prctr like bsis-prctr,

zztitle like bsis-zztitle,

zzterr like bsis-zzterr,

end of i_data.

and t_data has more fields including the above in the same order.

Thanks

Re: Performance issue

Former Member — Thu, 19 Oct 2006 20:49:49 GMT

mahulenka

the structures if i_data and t_data are not same,

it is like this,

data: begin of i_data occurs 0,

bukrs like bsis-bukrs,

hkont like bsis-hkont,

augdt like bsis-augdt,

augbl like bsis-augbl,

gjahr like bsis-gjahr,

belnr like bsis-belnr,

buzei like bsis-buzei,

budat like bsis-budat,

bldat like bsis-bldat,

xblnr like bsis-xblnr,

blart like bsis-blart,

shkzg like bsis-shkzg,

dmbtr like bsis-dmbtr,

wrbtr like bsis-wrbtr,

sgtxt like bsis-sgtxt,

aufnr like bsis-aufnr,

kostl like bsis-kostl,

dmbe2 like bsis-dmbe2,

prctr like bsis-prctr,

zztitle like bsis-zztitle,

zzterr like bsis-zzterr,

end of i_data.

and t_data has more fields including the above in the same order.

Thanks

Re: Performance issue

Former Member — Thu, 19 Oct 2006 20:49:55 GMT

mahulenka

the structures if i_data and t_data are not same,

it is like this,

data: begin of i_data occurs 0,

bukrs like bsis-bukrs,

hkont like bsis-hkont,

augdt like bsis-augdt,

augbl like bsis-augbl,

gjahr like bsis-gjahr,

belnr like bsis-belnr,

buzei like bsis-buzei,

budat like bsis-budat,

bldat like bsis-bldat,

xblnr like bsis-xblnr,

blart like bsis-blart,

shkzg like bsis-shkzg,

dmbtr like bsis-dmbtr,

wrbtr like bsis-wrbtr,

sgtxt like bsis-sgtxt,

aufnr like bsis-aufnr,

kostl like bsis-kostl,

dmbe2 like bsis-dmbe2,

prctr like bsis-prctr,

zztitle like bsis-zztitle,

zzterr like bsis-zzterr,

end of i_data.

and t_data has more fields including the above in the same order.

Thanks

Re: Performance issue

Former Member — Thu, 19 Oct 2006 20:50:15 GMT

mahulenka

the structures if i_data and t_data are not same,

it is like this,

data: begin of i_data occurs 0,

bukrs like bsis-bukrs,

hkont like bsis-hkont,

augdt like bsis-augdt,

augbl like bsis-augbl,

gjahr like bsis-gjahr,

belnr like bsis-belnr,

buzei like bsis-buzei,

budat like bsis-budat,

bldat like bsis-bldat,

xblnr like bsis-xblnr,

blart like bsis-blart,

shkzg like bsis-shkzg,

dmbtr like bsis-dmbtr,

wrbtr like bsis-wrbtr,

sgtxt like bsis-sgtxt,

aufnr like bsis-aufnr,

kostl like bsis-kostl,

dmbe2 like bsis-dmbe2,

prctr like bsis-prctr,

zztitle like bsis-zztitle,

zzterr like bsis-zzterr,

end of i_data.

and t_data has more fields including the above in the same order.

Thanks

Re: Performance issue

Former Member — Thu, 19 Oct 2006 21:00:55 GMT

I meant that you should use the logic to fill so_bukrs even if it has the range you specified. For now you can take out the IF SO_BUKRS IS INITIAL and ENDIF (for testing).

rob

Re: Performance issue

Former Member — Thu, 19 Oct 2006 21:31:58 GMT

hi Rob,

I understood.

I gave a range

so_bukrs 000 - 999

and your logic

so_bukrs-option = 'EQ'.

so_bukrs-sign = 'I'.

SELECT bukrs FROM t001

INTO so_bukrs-low.

APPEND so_bukrs.

ENDSELECT.

Is this the way you meant.

Now so_bukrs has values

000 999

004 999

005 999

006 999

999 999

Am I correct ?

please let me know.

Thanks

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.

Re: Performance issue

Former Member — Thu, 19 Oct 2006 21:38:24 GMT

No - you have to refresh the table before doing this. You want to end up with a range table (or select-option) that has only the values of the company codes in your system and is testing for equality for each of them.

rob

Re: Performance issue

Former Member — Thu, 19 Oct 2006 21:40:40 GMT

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.

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.

Re: Performance issue

Former Member — Thu, 19 Oct 2006 21:42:18 GMT

I think

i need to refresh the so_bukrs before using your logic.

and I can clear so_bukrs-high becaue we are using EQ sign

Right !