Performance issue

[Former Member]

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

Thanks

[Former Member]

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

[Former Member]

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

[Former Member]

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

Rob

[Former Member]

It is returning only 2000 records after all the search.

but its taking long time.

please let me know an example

thanks rob

Thanks

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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 !

[Former Member]

Rob,

I did like you said, let me test it and let you know.

Amit,

I will try your suggestion.

Thanks

[Former Member]

That's correct.

But I have to say that I did some testing and a range seemed to work pretty well.

So, if none of this does help, would you please do a performance trace (ST05) on this? It should show where the bottleneck is. Also press the 'explain' button. This will tell you which index (if any) is being used.

Rob

[Former Member]

Rob,

Can you please explain me how does where condition on select-option or range works.

Now so_bukrs has values,

000

004

005

006

.

.

.

.

.

999

how does the select statement work it will check for each CC at a time.

and

I have a question, If the user gives a single company code then our code wont work , we dont have to select all CC's Right !

[Former Member]

This is just for testing to see if it runs more quickly than before. If it does, then you will make adjustments afterwards.

Do you actually have 1,000 company codes? If you do, I don't think it will work.

Rob

[Former Member]

We have 90 CC's only.

I have scheduled BGD job using ranges for CC.

I need to wait for a while.

I tested on fore ground while debugging there was improvement in selection for CC 000 - 999

it selected like 27,000,000 doc's and came to next step.

when i pressed F5 it terminated the error was total memory was used.

but atlease it selected the doc's earlier it terminated after 700 secs maximum run time exceeded.

if it works better in BGD, then I need to make changes if User gives a single CC or diff range of CC's

one more question

How do I assign points to people.

Thanks Rob.

[Former Member]

Well, I'm glad it's helping.

I think there should be radio buttons with stars beside each resonse to your question. If you select the radio buttons, that will assign points.

In any event, I think you should close this post by marking it as solved (it's pretty long) and open a new one if you have further questions.

Rob

[Former Member]

Hi Rob,

Thanks Rob, It is wokring better than earlier

OK I have marked this issue as Answered.

Thanks to all.

[Former Member]

Hi Chary - I appreciate the points, but you should also check the "Rules of Engagement" at the top of the ABAP General Forum. It gives some ideas about how to allot points.

Basically, other people put some time and thought into helping you. They should also share.

Rob

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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