BKPF - Performance issue

[Former Member]

Hi ,

We have 3.5 million records in BKPF. we an have application which search the documents based on document type, doc status, doc date and posting date. The below select statement taking 2mins to get the data.

- Package size is used to avoid shortdumps/memory issues as it is huge table

- Once I receive the data into internal table, i will filter based on posting date and doc date and any other filters.

    SELECT bukrs belnr gjahr blart bldat budat USNAM XBLNR BKTXT BSTAT

      FROM bkpf

      INTO TABLE gt_bkpf_tmp package size 50000

     WHERE bukrs EQ '1010'

       AND ( bstat = 'V' OR bstat = '' OR bstat NE 'Z' ) "IN gr_bstat

       AND ( blart = 'AB' OR blart = 'SA' OR blart = 'IS' OR blart = 'SK' )

      if sy-subrc = 0.

        append lines of gt_bkpf_tmp to gt_bkpf.

        free: gt_bkpf_tmp.

      endif.

    endselect.

Any inputs to improve the performance?

Any function module i can use to retreive the data from BKPF?

I used FM "GLE_MCA_READ_BKPF", but it is not of much use. Above select statement is running much faster as we are using index (BKPF~3)

Thanks

[mayur_priyan]

Try FETCH and OPEN CURSOR method. This might improve your performance.

http://wiki.scn.sap.com/wiki/display/ABAP/FETCH+and+OPEN+CURSOR+Analysis

Regards,

Mayur Priyan. S

[Former Member]

Hi Reddy,

     Please avoid using "OR" in your WHERE clause. You might want to try using IN. Please see sample code below. Hope this helps.

SELECT bukrs belnr gjahr blart bldat budat USNAM XBLNR BKTXT BSTAT

      FROM bkpf

      INTO TABLE gt_bkpf_tmp package size 50000

     WHERE bukrs EQ '1010'

       AND bstat in ( 'V', ' ',)

       AND bstat NE 'Z'

       AND blart in ('AB','SA','IS','SK' ).

Best Regards,

Charlie

[Former Member]

Hi Charlie,

Initially i went with ranges using "IN". But later I found that it is not picking correct index (BKPF~3).

So i have to change from "IN"to ËQ". Now it is picking correct index.

The problem i have is when they search with posting date(BUDAT) or document date(BLDAT), I have to pull all the entries into internal table and filter them using BUDAT and BLDAT.

Ex: If they want to see last 5 days of data, i will be pulling around 250,000 records into internal table using the above statement and later filter with BUDAT and BLDAT, the final internal table will be around 50 records.

I have to no choice as i do not have index with these fields .

BUKRS

BLART

BSTAT

BUDAT

BLDAT

If I place BUDAT or BLDAT to the above statement, the performance is much worse.

[Former Member]

Hi Reddy,

     Reason why I think, u are not getting the correct data, its because you are limiting your select statement through Package. Hope this helps. May I see your code including the DATE filter.

Best Regards,

Charie

[Former Member]

Reason why I think, u are not getting the correct data, its because you are limiting your select statement through Package.

I think we are deviating here, data is coming correctly. It's just that to see 5 days old data, i have to pull 250,000 records(with my doc types and status) and filter them to 50 records using BLDAT and BUDAT.

I used package to avoid memory issues as i'm dealing with large table.

Planning to create secondary index for the below fields.

MANDT

BUKRS

BLART

BLDAT

BUDAT

What do you think? Will it impact on any other areas (while INSERT or UPDATE the documents)??

[Former Member]

If you use BSEG/BKPF you should use year/date in where clause or else the company code uniqueness are very less obviously performance will be poor.

[Former Member]

Hi,

Planning to create secondary index for the below fields.

MANDT

BUKRS

BLART

BLDAT

BUDAT

What do you think? Will it impact on any other areas (while INSERT or UPDATE the documents)??