Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results forΒ 
Search instead forΒ 
Did you mean:Β 

Performance Issues on BSID and BSAD

Former Member
0 Kudos

Hello guys,

for a custom report I need the balance data of a couple of vendors for different keydates. Therefor I tried to use function 'BAPI_AR_ACC_GETKEYDATEBALANCE' which works fine but takes a lot of performance time when getting data for more than one vendor or more than one key date. The function mainly reads BSID and BSAD tables which is enough for my report, so I tried to get it faster with creating my own select.

When looking at the popular SAP boards you get different opinions about the best way for the performance in SELECTS. So can you please tell me what you think would be the best way for tables BSID and BSAD?

I got the key fields company code as parameter and customer as range. I also need only normal documents (BSTAT = ' ') and only documents until one or more keydates as range (BUDAT <= keydate).

So what would be the best way?

(1) Moving the whole BSID/BSAD fields into local BSID/BSAD table or just the fields that I need (see example)?

(2) Doing one SELECT for every vendor no. in the range or getting all vendor no in one select?

Any other performance hints? πŸ™‚


   SELECT bukrs kunnr budat shkzg dmbtr pswsl pswbt FROM bsid
     INTO CORRESPONDING FIELDS OF TABLE gt_bsid
     WHERE bukrs EQ pa_bukrs
     AND   kunnr IN gr_kunnr
     AND   budat LE pa_stich-high
     AND   bstat EQ ''.


   SELECT bukrs kunnr augdt budat shkzg dmbtr pswsl pswbt FROM bsad
     INTO CORRESPONDING FIELDS OF TABLE gt_bsad
     WHERE bukrs EQ pa_bukrs
     AND   kunnr IN gr_kunnr
     AND   budat LE pa_stich-high
     AND   augdt GT pa_stich-high
     AND   bstat EQ ''.

Thanks a lot!

Regards
Michael

8 REPLIES 8

Former Member
0 Kudos

Hi

The best way to read BSAD/BSID is used BUKRS and KUNNR in WHERE conditions, so your selection are ok.

If you want to try to improve the performace you should do one selection (else it would be same kind of selection of the BAPI), so your selections are ok,

If you need to use only certain fields it's better to indicate them in extraction so your selection are ok.

I mean the selections you've posted should be the best way to read BSID/BSAD, the problem could be in how many records could be extracted: it can risk a dump for memory.

Max

murtuza_chhatariya
Active Participant
0 Kudos

Hi Michael,

(1) Moving the whole BSID/BSAD fields into local BSID/BSAD table or just the fields that I need (see example)?

Best practice is to select only fields which are required

so I suggest to remove INTO CORRESPONDING FIELDS OF TABLE and declare type of gt_bsid / gt_bsad with only list of required fields and use INTO TABLE.

(2) Doing one SELECT for every vendor no. in the range or getting all vendor no in one select?

One select would be faster then individual selects.

Also few more points..

- remove GT or LT conditions in SELECT where clause and do it on internal table after SELECT.

- change the order of BUDAT and AUGDT in where clause of BSAD select. it should be as per table's field order like below.

"AND   augdt GT pa_stich-high

AND   budat LE pa_stich-high"

Regards,

Murtuza

raymond_giuseppi
Active Contributor
0 Kudos

Almost always prefer one SELECT to a SELECT IN LOOP.

Another solution could be reading also some total table :

  • KNC1 (no special G/L, Balance Carried Forward + total DB/CR per period, one record per Fiscal Year)
  • KNC3 for special G/L (one record per fiscal year and special G/L indicator, only one total per year)

Only restrict access to BSID/BSAD for dates that are not end of period (or don't allow those...)

NB: From my experience, you can keep the INTO CORRESPONDING, just define a structure with only required fields, the actual generated SQL statement will only ask for those fields (read also Why "INTO CORRESPONDING" is much better than its reputation)

Regards,

Raymond

Former Member
0 Kudos

@Murtuza


- remove GT or LT conditions in SELECT where clause and do it on internal table after SELECT.

I do not know about that, this would mean reading all entries for each vendor. If e.g. the key date is in 2010, I would read items for 4 years which I do not need.

@Raymond

table KNC1 looks interesting, but it is only per month or what mean the 16 value fields? I think it is for every period isn't it?

But I need it per special keydate, e.g. today 18.09.2014. But indeed it can help me with taking out lots of items from previous years. How would I have to do this?

Example if I want the balance from today: Reading table KNC1 balance carried forward value for year 2014 and then adding all items from BSID and BSAD from 01.01.2014 until today?

0 Kudos

I do not know about that, this would mean reading all entries for each vendor. If e.g. the key date is in 2010, I would read items for 4 years which I do not need.

Actually yes. But it depends on result; if you remove LT or GT condition and there are unexpectedly high volume of records, then probably we should let these conditions as they are.

If there are only few extra records per primary key then we can remove those conditions and apply DELETE - WHERE on internal table.

Generally GT /  LT / NE comparisons at database level take extra time so it is not preferable to add in WHERE clause and performance can be improved.

0 Kudos

Yes to get a balance at some dates, read KNC1 for the firstst year required, and only read BSID/BSAD from start of first month/period on

Regards,

Raymond

0 Kudos

Yes I tried this and it looks good, particularly in performance of course.

But I found out some problems in that method.

(1) As I can see the values are only in local currency? Is there any way to also find the document currcency?

(2) In the first days of the new year the balance carry forward is not done yet, so I have to go one year back and get the carry forward of the previous year?

Regards
Michael

s_nnoorie
Active Participant
0 Kudos

Dear Michael, I had similar issue to get data of all vendors/customer present in few G/L  and I solved using the following.

  • I selected all the key fields + fields required (i.e from bukrs to buzei + required field)
  • Avoid "INTO CORROSPONDING FIELDS" instead use "INTO TABLE".
  • use key fields in where condition as much as possible.
  • Avoid "FOR ALL ENTRIES" if possible
  • Avoid using >,<,GE etc., instead use range (select-option or Ranges).

hope this will help.