Performance Issue with BSIS(open accounting items)

[Former Member]

Hey All,

I am having serious performance issue with a accrual report which gets all open GL items, and need some tips for optimization.

The main issue is that I am accesing large tables like BSIS, BSEG, BSAS etc without proper indexes and that I am dealing with huge amounts of data.

The select itself take a long time and after that as I have so much data overall execution is slow too.

The select which concerns me the most is:

  SELECT zuonr hkont gjahr belnr buzei budat blart wrbtr shkzg xblnr waers bukrs
             INTO TABLE i_bsis
              FROM bsis
              WHERE bukrs = '1000'
              AND hkont in r_hkont    
              AND budat <= p_lcdate
              AND augdt = 0
              AND augbl = space
              AND gsber = c_ZRL1    
              AND gjahr BETWEEN l_gjahr2 AND l_gjahr
              AND ( blart = c_re      "Invoice
              OR    blart = c_we      "Goods receipt
              OR    blart = c_zc      "Invoice Cancels
              OR    blart = c_kp ).   "Accounting offset

I have seen other related threads, but was not that helpful.

We already have a secondary index on bukrs hkont and budat, and i have checked in ST05 that it does use it. But inspite that it takes more than 15 hrs to complete(maybe because of huge data).

Any Input is highly appreciated.

Thanks

[Former Member]

Hello,

to create a secondary index you have to create it with exactly the same fields you are using on your where condition....

 WHERE bukrs = '1000'
              AND hkont in r_hkont    
              AND budat <= p_lcdate
              AND augdt = 0
              AND augbl = space
              AND gsber = c_ZRL1    
              AND gjahr BETWEEN l_gjahr2 AND l_gjahr
              AND ( blart = c_re      "Invoice
              OR    blart = c_we      "Goods receipt
              OR    blart = c_zc      "Invoice Cancels
              OR    blart = c_kp ).   "Accounting offset

So your index must be like this...

bukrs
hkont 
budat
augdt
augbl
gsber
gjahr
blart.

If you dont create the idex that way, ABAP will not use it...

Be aware that if your table already has several indexes, other select stms will be slower... ask your basis team about it.

Bye

Gabriel P...

[Former Member]

>

> Hello,
> 
> to create a secondary index you have to create it with exactly the same fields you are using on your where condition....

That's incorrect. The SELECT will be able to use the index he has created, but may not be able to use it effectively because it's not selective enough.

Rob

[Former Member]

Hello Rob,

Thanks!! I´ve been thinking wrong!!...I was thinking that it was true....sorry all.

Now I may have to change tons of abap code...je je

Bye

Gabriel P.-

[Former Member]

A couple of thoughts:

In BSIS, AUGDT and AUGBL will always be initial, so you can simply remove that from the WHERE.

Having a secondary index on BUKRS HKONT and BUDAT may not help much if P_LCDATE is recent. It may confuse the database optimizer into doing a table scan.

You should run ST05 against this SELECT and have a look at the explain to see which index is being used.

Does R_HKONT contain values? Are they all EQ or small ranges.

Rob

[Former Member]

Thanks for your responses.

Gabriel,

This is an existing report which I am trying to optimize. The index was already created. And i have said this before in my first post that the select does use the index which I verified in ST05. Thanks for you input though.

Rob,

I did check ST05 and it does uses the secondary index, I checked this in DEV(QA takes too long), and i think it should behave the same everywhere.

SELECT STATEMENT ( Estimated Costs = 1 , Estimated #Rows = 1 )

       3 FILTER
         Filter Predicates

           2 TABLE ACCESS BY INDEX ROWID BSIS
             ( Estim. Costs = 1 , Estim. #Rows = 1 )
             Estim. CPU-Costs = 11,619 Estim. IO-Costs = 1
             Filter Predicates

               1 INDEX RANGE SCAN *BSIS~Z1*
                 ( Estim. Costs = 1 , Estim. #Rows = 1 )
                 Search Columns: 4
                 Estim. CPU-Costs = 9,913 Estim. IO-Costs = 1
                 Access Predicates Filter Predicates

R_HKONT does have values but there are ranges BT, not equal.

Joyjit,

I will try your suggestion.

Thanks

[Former Member]

>

> Rob,
> I did check ST05 and it does uses the secondary index, I checked this in DEV(QA takes too long), and i think it should behave the same everywhere.
> 

It will not necessarily behave the same on different instances. You should run ST05 in QA using the same criteria that take a long time.

Rob

[Former Member]

Here is the analysis after running the loooong job in QA. Based on ST05, it seems the select is using the index Z1, but the estimated cost is too high. Also when i checked in SM50 it was showing that the select was doing a sequential read(but i guess this is because i am using ranges and or conditions in the where).

SELECT STATEMENT ( Estimated Costs = 319 , Estimated #Rows = 1 )

       3 FILTER
         Filter Predicates

           2 TABLE ACCESS BY INDEX ROWID BSIS
             ( Estim. Costs = 319 , Estim. #Rows = 1 )
             Estim. CPU-Costs = 9,779,108 Estim. IO-Costs = 318
             Filter Predicates

               1 INDEX RANGE SCAN BSIS~Z1
                 ( Estim. Costs = 268 , Estim. #Rows = 1,644 )
                 Search Columns: 4
                 Estim. CPU-Costs = 9,211,584 Estim. IO-Costs = 267
                 Access Predicates Filter Predicates

The select on BSIS take 2 hrs 30 mins and returns 1.5 million entries.

Joyjit,

Your tweak does bring the cost a little down, but i am not sure how much time will that save me.

Hadi,

Your suggestion was my first intended approach, but I am not so sure if I can assume BSIS-BELNR and BSIS-BUZEI to be unique as document numbers can be reused in a different year and I am pulling data for 3 years. Thanks for your suggestion.

Any other inputs.

[Former Member]

Check this variant of ur select:

l_date = '19000101'.

SELECT zuonr hkont gjahr belnr buzei budat blart wrbtr shkzg xblnr waers bukrs

INTO TABLE i_bsis

FROM bsis

WHERE bukrs = '1000'

AND hkont in r_hkont

AND budat BETWEEN l_date and p_lcdate

AND augdt = 0

AND augbl = space

AND gsber = c_ZRL1

AND gjahr BETWEEN l_gjahr2 AND l_gjahr

AND blart in ( c_re, c_we, c_zc , c_kp ).

[Former Member]

Try to select it package-wisely. I assume here that fields BSIS-BELNR and BSIS-BUZEI of each record are unique in your case. Your coding would look like this:

data:   max_belnr like BSIS-BELNR,
        max_buzei like BSIS-BUZEI,
        all_data_processed(1) type c .

WHILE all_data_processed IS INITIAL.
  SELECT zuonr hkont gjahr belnr buzei budat blart wrbtr shkzg xblnr waers bukrs
            UP TO 1000 ROWS
             INTO TABLE i_bsis
              FROM bsis
              WHERE bukrs = '1000'
              AND hkont in r_hkont    
              AND budat <= p_lcdate
              AND augdt = 0
              AND augbl = space
              AND gsber = c_ZRL1    
              AND gjahr BETWEEN l_gjahr2 AND l_gjahr
              AND ( blart = c_re      "Invoice
              OR    blart = c_we      "Goods receipt
              OR    blart = c_zc      "Invoice Cancels
              OR    blart = c_kp )
              AND belnr  GT max_belnr
              AND buzei  GT max_buzei.

   IF sy-subrc EQ 0.

     SORT i_bsis by belnr buzei.

     READ TABLE i_bsis INDEX sy-dbcnt.
     max_belnr  = i_bsis-belnr.
     max_buzei  = i_bsis-buzei.

*    Do whatever you've got to do with the selected data in the internal table i_bsis.
     LOOP AT i_bsis.
        ...
     ENDLOOP.    
 
   ELSE.
     all_data_processed = 'X'.
   ENDIF.
ENDWHILE.

Hope that helps.

[ThomasZloch]

You said that R_HKONT contains several ranges of account numbers. If these ranges cover a significant portion of the overall existing account numbers, then there is no really quick access possible via the BSIS primary key.

As Rob said, your index on HKONT and BUDAT does not help much, since you are selecting "<=" on BUDAT. No chance of narrowing down that range?

What about GSBER? Does the value in c_ZRL1 provide a rather small subset of the overall values? Then an index on BUKRS and GSBER might be helpful.

I assume that the four document types are not very selective, so it probably does not pay off to investigate selecting on BKPF (there is an index involving BLART) and joining BSIS for the additional information. You still might want to look into it though.

Thomas

[Former Member]

Thank you Thomas for your inputs:

You said that R_HKONT contains several ranges of account numbers. If these ranges cover a significant
 portion of the overall existing account numbers, then there is no really quick access possible via the 
BSIS primary key.

Unfortunately R_HKONT contains all account numbers.

As Rob said, your index on HKONT and BUDAT does not help much, since you are selecting "<=" on 
BUDAT. No chance of narrowing down that range?

Will look into this.

What about GSBER? Does the value in c_ZRL1 provide a rather small subset of the overall values? Then
 an index on BUKRS and GSBER might be helpful.

ZRL1 does provide a decent selection . But I dont know if one more index is a good idea on overall

system performance.

I assume that the four document types are not very selective, so it probably does not pay off to 
investigate selecting on BKPF (there is an index involving BLART) and joining BSIS for the additional 
information. You still might want to look into it though.

I did try to investigate this option too. Based on other threads related to BSIS and Robs Suggestion in

those threads I tried this:

SELECT bukrs belnr gjahr blart budat
  FROM bkpf INTO TABLE bkpf_l
        WHERE bukrs = c_pepsico
        AND bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z')
        AND blart IN ('RE', 'WE', 'ZC', 'KP')
        AND gjahr BETWEEN l_gjahr2 AND l_gjahr
        AND budat <= p_lcdate.

 SELECT zuonr hkont gjahr belnr buzei budat blart wrbtr shkzg xblnr waers bukrs
           FROM bsis INTO TABLE i_bsis FOR ALL ENTRIES IN bkpf_l
                     WHERE bukrs = bkpf_l-bukrs
                      AND  hkont IN r_hkont
                      AND  budat = bkpf_l-budat
                      AND  augdt = 0
                      AND  augbl = space
                      AND  gjahr = bkpf_l-gjahr
                      AND  belnr = bkpf_l-belnr
                      AND  blart = bkpf_l-blart
                      AND  gsber = c_zrl1.

The improves the select on BSIS a lot, but the first select on BKPF kills it. Not sure if this would help

improve performance.

Also I was wondering whether it helps on refreshing the tabe statistics through DB20. The last refresh

was done 7 months back. How frequently should we do this? Will it help?

[valter_oliveira]

It depends, but you should run statistics weekly (at least).

Regards.

Valter Oliveira.

[ThomasZloch]

> ZRL1 does provide a decent selection . But I dont know if one more index is a good idea on overall

> system performance.

Cost/benefit has to weighed. Cost is more disk space taken and a little more overall processing effort for the database when changing BSIS data. It sounds like your benefit would be quite large. Maybe you can even ditch your Z1-index if this works.

>

> SELECT bukrs belnr gjahr blart budat
>   FROM bkpf INTO TABLE bkpf_l
>         WHERE bukrs = c_pepsico
>         AND bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z')
>         AND blart IN ('RE', 'WE', 'ZC', 'KP')
>         AND gjahr BETWEEN l_gjahr2 AND l_gjahr
>         AND budat <= p_lcdate.
> 

Do you really need all those BSTAT values? Most of the time, BSTAT = space for actually posted documents is sufficient. Did you trace and make sure that index BKPF~3 is being used?

> Also I was wondering whether it helps on refreshing the tabe statistics through DB20. The last refresh

> was done 7 months back. How frequently should we do this? Will it help?

Not sure about normal intervals, but if in doubt it cannot hurt to refresh the statistics, I'd say.

Cheers

Thomas

[valter_oliveira]

I think that Thomas solution would be the best if:

1 - restricting for only one (c_ZRL1) division will be a good data filter. If the majority of those 1.5 million records have that c_ZRL1 forget it ... if there are 100 thousand ... probably a good choice!

2 - your program will run with some regularity! To run once a month, or a year, I wouldn't recomend an index. If it will run daily, go for it!

But I dont know if one more index is a good idea on overall system performance

An index with fields BUKRS/GSBER won't make an issue on that. But remember that if you do so, run optimizer statistics after!

Regards,

Valter Oliveira.

[Former Member]

Review Oss note 194842 regarding poor performance when displaying line items.

Manoj