Skip to Content
author's profile photo Former Member
Former Member

Performance issue

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 19, 2006 at 04:50 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • author's profile photo Former Member
    Former Member
    Posted on Oct 19, 2006 at 06:59 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted 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
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted 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
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.