bkpf performance

[Former Member]

Hi all,

please check the below select statement i have been using and i am unable to get the data even after 30 minutes.

please suggest any changes to increase the performance.

SELECT vbeln waerk fkdat knumv kunag kalsm fkart vbtyp kurrf

INTO CORRESPONDING

FIELDS OF TABLE i_vbrk FROM vbrk

WHERE vbtyp IN ('M','U')

AND fkdat IN s_fkdat

AND kunag IN s_kunnr.

LOOP AT i_vbrk.

i_vbrk-vbeln1 = i_vbrk-vbeln.

MODIFY i_vbrk.

ENDLOOP.

SORT i_vbrk BY vbeln1.

IF NOT i_vbrk[] IS INITIAL.

SELECT bukrs belnr gjahr budat awkey INTO TABLE i_bkpf FROM bkpf

FOR ALL ENTRIES IN i_vbrk

WHERE bukrs = '1000'

AND blart = 'RV'

• and blart IN ('RE','RV')

AND awkey = i_vbrk-vbeln1. "Index BKPF~3 used.

ENDIF.

[Former Member]

Hi,

At the first place please review your query of BKPF.

"BLART = 'RV' and BLART in ('RE', 'RV') ". This is confusing - do you want to fetch all the records that have BLART = RV or BLART = 'RE' ?; Need to change the and clause to include only the clause left to AND or right to it....

Try after correcting this query.

Regards,

Girish

[Former Member]

hi,

there is a standard index in bkpf, for awtyp, awkey and awsys.u can use this,

try,

data : lv_logsys like TBDLS-LOGSYS .

call function 'OWN_LOGICAL_SYSTEM_GET'

importing

OWN_LOGICAL_SYSTEM = lv_logsys .

SELECT bukrs belnr gjahr budat awkey INTO TABLE i_bkpf FROM bkpf

FOR ALL ENTRIES IN i_vbrk

WHERE bukrs = '1000'

AND blart = 'RV'

and blart IN ('RE','RV')

and awtyp = 'VBRK'

AND awkey = i_vbrk-vbeln1. "Index BKPF~3 used

and awsys = lv_logsys .

it will be faster...

[Former Member]

Hi, Please check the below modified logic if that helps you to increase the performance.

• Define your internal table with the field anmes as in data base and also foolow the same sequence as in select query. Then by this you can remove the "corresponding fields of" statament from the below query. Also if possible please have an index on the FKDAT field in VBRK table.

SELECT vbeln waerk fkdat knumv kunag kalsm fkart vbtyp kurrf

INTO TABLE i_vbrk FROM vbrk

WHERE vbtyp IN ('M','U')

AND fkdat IN s_fkdat

AND kunag IN s_kunnr.

i_vbrk_temp[] = i_vbrk[]. (By this you can avoid the loop).

SORT i_vbrk_temp BY vbeln1.

• By the below delete statements you can get the unique values (non-initial).

Delete adjacent duplicates from i_vbrk_temp comparing vbeln.

DELETE from i_vbrk_temp WHERE vbeln IS INITIAL.

IF NOT i_vbrk_temp[] IS INITIAL.

• Here rather than using the AWKEY (Object key) please use the reference document number (XBLNR) in the select query.

This will increase the performance in significant way.

SELECT bukrs belnr gjahr budat awkey INTO TABLE i_bkpf FROM bkpf

FOR ALL ENTRIES IN i_vbrk

WHERE bukrs = '1000'

AND XBLNR = i_vbrk-vbeln1

AND blart = 'RV'. "Index 1 Used

ENDIF.

Thanks,

- Srikanth.

Edited by: srikanth kamisetti on May 5, 2008 9:03 PM