BSIS performance

[Former Member]

Check for duplicates

SELECT zuonr

budat

wrbtr

FROM bsis

into table i_bsis

for all entries in i_indata1

WHERE bukrs = 'BP01' and

gjahr = sy-datum+0(4) and

budat = i_indata1-t_pdate AND

zuonr = i_indata1-t_kioid.

When i am trying to select data from bsis i get time out error. Is there a way to avoid this?

[Former Member]

Can you check this table has any Secondary Index ,if so use in where condition.

also run the program in background

[Former Member]

Currently i am pulling the file from the local system. so i can't run in background.

secondly, when i hit on index in the bsis table i don't see any indexes.

Some one let me know if i can do anything with indexes to come out of it.

[Former Member]

Hi Vinni,

Try implementing the code I suggested in my previous post. If it does not solve the problem you can write to a UNIX file using OPEN DATASET, TRANSFER, CLOSE DATASET and then use transaction CG3Y to transfer the data from the UNIX server to your desktop.

[Former Member]

Your code does not make sure that internal table i_indata1 is empty before using it in the for all entries clause. If internal table i_indata1 is empty SAP will attempt to retrieve all the records from database table BSIS into internal table i_bsis. This can be time consuming and unnecessary. Besides it can also cause an ABAP dump if the size of the internal table is greater than the maximum permissible size set by the BASIS team at the time of installation of SAP. In addition you need to check for duplicate entries in internal table i_indata1 comparing the two fields used in the where clause. If you don't the number of iterations at the database server increases.

Try using the following logic.

IF NOT i_indata1[] IS INITIAL.
  i_indata1_tmp[] = i_indata1[].
  SORT i_indata1_tmp BY t_kioid
                        t_pdate.
  DELETE ADJACENT DUPLICATES FROM i_indata1_tmp
    COMPARING t_kioid
              t_pdate.
  SELECT zuonr
         budat
         wrbtr
         hkont
         augdt
         augbl
         belnr
         buzei
  FROM bsis
  INTO TABLE i_bsis
  FOR ALL ENTRIES IN i_indata1_tmp
  WHERE bukrs EQ 'BP01'
  AND   zuonr EQ i_indata1_tmp-t_kioid
  AND   gjahr EQ sy-datum+0(4)
  AND   budat EQ i_indata1_tmp-t_pdate.
ENDIF.

[Former Member]

Hi,

I have similar problem ..

I need to update business area field in BSEG, GLPCA and BSIS table. I don't have any problem with updating BSEG and GLPCA table . But when it updating BSIS table , getting time out..

Code is like this...

it_glpca-gsber = izjvup_update-gsber.

MODIFY it_glpca TRANSPORTING gsber.

it_bseg-gsber = izjvup_update-gsber.

MODIFY it_bseg TRANSPORTING gsber

it_glpca-gsber = izjvup_update-gsber.

MODIFY it_glpca TRANSPORTING gsber.

it_bseg-gsber = izjvup_update-gsber.

MODIFY it_bseg TRANSPORTING gsber

UPDATE glpca FROM TABLE it_glpca.

UPDATE bseg FROM TABLE it_bseg.

UPDATE bsis FROM TABLE it_bsis.

Thanks

[Former Member]

These are tables you <b><i>never</i></b> update on your owns.

Rob

[Former Member]

Hi

Write like this

data : v_year(4).

v_year = sy-datum+0(4).

if not i_indata1[] is initial.

SELECT zuonr

budat

wrbtr

FROM bsis

into table i_bsis

for all entries in i_indata1

WHERE bukrs = 'BP01' and

gjahr = v_year and

budat = i_indata1-t_pdate AND

zuonr = i_indata1-t_kioid.

endif.

reward points if useful

Regards

anji

[Former Member]

Hi, MOve the data from budat and zuonr fields of i_indata1

table into a temporary int table.

loop at i_indata1 .

move i_indata1-t_pdate to itab-budat.

move i_indata1-t_kioid to itab-zuonr.

append itab.

clear itab.

endloop.

sort itab by budat zuonr.

delete adjacent duplicates from itab.

if not itab[] is initial.

SELECT zuonr

budat

wrbtr

FROM bsis

into table i_bsis

for all entries in itab

WHERE bukrs = 'BP01' and

gjahr = sy-datum+0(4) and

budat = itab-t_pdate AND

zuonr = itab-t_kioid.

endif.

[Former Member]

Thanks every one for the replies. I agree with you reg. the table empty check and delete adjacent duplicates but i am pretty much sure that in my scenario

1) there is always going to be one zunor for a particular date. so, no duplicates.

2) there is lot of data in indata1, actually thousands of records

i am still getting the time out error. So, i am looking for a solution like if i can use any indexes is it going to help me? but i don't see any index on table. Can i create one, and is it going to help me.

[Former Member]

Hi Vinni,

Try if you can get as pass as many key fields as possible.

The fuller the key, the faster is the result.

You can create a index on the table as a last resort.

Regards,

ravi

[Former Member]

That is the fullest key i could give.

And i am not pretty much sure how to create the index.

[Former Member]

Hi Vinni,

The BSIS table will always be very huge in data.

Before creating the index make sure that you have checked all other options such as querying from some other table if possible, providing the full key ,etc.

The decision to create an index on this table should be very last decision as it is used at many places.

Regards,

Atish

[Former Member]

In that case try writing to a UNIX file (OPEN DATASET), run the program in the background and use transaction GC3Y after the program has run to move the file being downloaded from UNIX to your desktop.

If there are a large number of records, you will not be able to debug it because the SAP programs can be run online only for a certain amount of time after which they timeout. This program will have to run in the background.

[Former Member]

Well, your problem is that your going against the wrong table for the key fields you have. Go against BKPF instead, (but add BSTAT to the WHERE).

Rob

[Former Member]

Like this:

REPORT ztest NO STANDARD PAGE HEADING LINE-SIZE 80   MESSAGE-ID 00.

TABLES: bkpf, bseg, bsis.

DATA: bkpf_int TYPE TABLE OF bkpf WITH HEADER LINE,
      bseg_int TYPE TABLE OF bseg WITH HEADER LINE,
      bsis_int TYPE TABLE OF bsis WITH HEADER LINE,

      BEGIN OF i_indata1 OCCURS 0,
        t_pdate TYPE bkpf-budat,
        t_kioid TYPE bseg-zuonr,
      END   OF i_indata1.

SELECT bukrs belnr gjahr budat
  FROM bkpf INTO CORRESPONDING FIELDS OF TABLE bkpf_int
  FOR ALL ENTRIES IN i_indata1
  WHERE bukrs = 'BP01' AND
        bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z') AND
        gjahr = sy-datum+0(4) AND
        budat = i_indata1-t_pdate.

SELECT bukrs belnr gjahr buzei zuonr wrbtr shkzg
  FROM bseg INTO CORRESPONDING FIELDS OF TABLE bseg_int
  FOR ALL ENTRIES IN bkpf_int
  WHERE bukrs = bkpf_int-bukrs AND
        belnr = bkpf_int-belnr AND
        gjahr = bkpf_int-gjahr AND
        koart = 'S'.                     "GL Accounts

SORT: bkpf_int BY bukrs belnr gjahr,
      bseg_int BY bukrs belnr gjahr buzei,
      i_indata1 BY t_kioid.

LOOP AT bseg_int.
  READ TABLE i_indata1 WITH KEY
    t_kioid = bseg_int-zuonr
    BINARY SEARCH.
  CHECK sy-subrc = 0.
  READ TABLE bkpf_int WITH KEY
    bukrs = bseg_int-bukrs
    belnr = bseg_int-belnr
    gjahr = bseg_int-gjahr
    BINARY SEARCH.
  CHECK sy-subrc = 0.
  MOVE: bkpf_int-budat TO bsis_int-budat,
        bseg_int-zuonr TO bsis_int-zuonr,
        bseg_int-wrbtr TO bsis_int-wrbtr,
        bseg_int-shkzg TO bsis_int-shkzg.
  APPEND bsis_int.
ENDLOOP.

Rob

[Former Member]

Hi,

Now that i have the key, i should go to BSIS?

This sounds good but instead of going to one table getting the data from 3 tables, i am not sure how efficient it is.

Assignment no and posting date with dollar amounts for sales and returns is my internal table. When i am loading this revenue using FB50(this is a big file with thoughsands of records) when there is an interruption a record posted for an assignment(asset no) with the same date and amount should not get posted again. This is the check i want to put in the program. So, I approached BSIS. So, now one can give me a better solution.

Also, another problem i see is when i put a check on BSIS, i don't know if its a sales record or returns record. So, when i post a returns record, there is always a chance that a record with same assignment number, posting date and same amount can exist in sales. Then it throws an error as per my check. I want to distinguish between these two posting as well. Apart from the GL accounts is there any othe way to know.

Thanks.

[Former Member]

BUKRS, BELNR, GJAHR and BUZEI are not keys for BSIS. That's why I suggested BKPF and BSEG. So long as you have the keys for these table (and now you do), there should be no performance issue.

Going to three large tables with keys is generally much more efficient than going to one large one without.

Rob

[Former Member]

Hi,

The best solution for you is to go to BKPF and BSEG as you now has the full key for these table, BUT you should execute this program in background.

As your input file itself has lots of entries and you are querying a very big cluster table (BSEG) then there is a every possibility that your program will time out even if you have the full key if you execute it in foreground.

Regards,

Atish