Performance issues with BKPF and BSET.

[vimal]

Hi All,

I am doing select statement from two tables BKPF and BSET ,both the tables are taking too much time. Following is the query.

Select ... .... FROM BKPF into table itab2 for all entries in itab1 WHERE <primary keys> and XBLNR = itab1-Xblnr.

Select ... ... FROM BSET into table itab3 for all entries in itab2 WHERE BELNR = BELNR.

These two queries are taking two much time due to heavy data in BKPF and BSET is a cluster table.

So, any suggestions?

[Former Member]

Hi

What's in <primary keys>?

What's in itab1 and itab2?

Max

[vimal]

itab1 has got entries from RSEG and contains GJAHR,XBLNR ,EBELN

Select ... .... FROM BKPF into table itab2 for all entries in itab1 WHERE BUKRS = '7000' AND

GJAHR = itab1-GJAHR and XBLNR = itab1-Xblnr.

itab2 = the entries selected from BKPF.

Select .... .... FROM BSET into table itab3 for all entries in itab2 WHERE BELNR = itab2-BELNR.

If there is any other doubt let me know.

Thanks,

[Former Member]

Hi

It needs to make sure the internal table are not empty if it uses FOR ALL ENTRIES option

BKPF should have a standard index for reference number, you should use it, so:

RANGES R_BSTAT FOR BKPF-BSTAT.
IF NOT ITAB1 IS INITIAL.
Select ... .... FROM BKPF into table itab2 for all entries in itab1 
     WHERE BUKRS = '7000' 
         AND BSTAT  IN R_BSTAT
         AND XBLNR = ITAB1-XBLNR
        AND GJAHR = itab1-GJAHR.
 IF SY-SUBRC EQ 0.
  Select .... .... FROM BSET into table itab3 for all entries in itab2 
     WHERE BUKRS =  '7000'
         AND BELNR = iITAB2-BELNR
        AND GJAHR = ITAB2-GJAHR.
  ENDIF.
ENDIF.

Max

[vimal]

Hi,

In my case BKPF-BSTAT field is empty for all the records , so i cannot filter the data on the basis of this field.

BKPF still takes too much time after the above solution.

Please suggest.

[Former Member]

Hi

Yes I know,

infact I haven't filled the range, but I've inserted it just to trigger the index BUKRS, BSTAT and XBLNR

What's it in ITAB1?

Max

[vimal]

itab1 has got entries from RSEG and contains GJAHR,XBLNR ,EBELN

[Former Member]

So you need to get the accounting document generated by logistic invoice (MIRO)?

Max

[raymond_giuseppi]

Did you try to read BKPF~4 with AWTYP/AWKEY, where AWTYP = 'RMRP' and AWKEY = RBKP-BELNR + RBKP-GJAHR (or RSEG). (Then for BSET, cluster, use full primary key of cluster, as already written.)

Regards,

Raymond

[ThomasZloch]

Empty ranges are not being passed to the database, it's as if they are not included in the WHERE-condition at all, so try it with "BSTAT = space" in the BKPF select to make use of index BKPF~1, you usually don't want the other types of documents anyway in your result.

Thomas

[Former Member]

So

Just a little summury

If you need to read the accounting document generated by logistic invoice, it should be better to use the field AWTYP and AWKEY in the selection of BKPF just as Raymond said.

If you want to get all document having the same XBLNR you can use BUKRS, BSTAT (= space in your case) and XBLNR

The both selections will use standard indexes

If the selection still takes too long, try to trace it and check if the index is called, if it's not called you can force it in the selection.

Anyway it needs to understand how many hits are in your table ITAB1

Max

[ThomasZloch]

Yes, don't post place holders but the real deal if you want our experts to spent their time on your issue.

Don't forget that BSET wants to be searched with full primary key as well. Being a cluster table is not an issue, apart from the limitation that you cannot have secondary indexes for these.

Thomas