topic Re: Poor performance in VBFA select statement in Application Development and Automation Discussions

Poor performance in VBFA select statement

former_member259894 — Sun, 29 Apr 2012 11:10:53 GMT

Hi SAP Gurus,

I have a VBFA select statement which goes something like this:

select vbelv posnv posnn vbtyp_v from vbfa

into table i_vbfa

for all entries in is_bil_invoice-it_gen

where vbeln = is_bil_invoice-it_gen-bil_number

and posnn = is_bil_invoice-it_gen-itm_number

and ( vbtyp_n = 'M' or vbtyp_n = 'N' ).

However this statement takes around one whole minute to execute even in Quality.

Please help me optimize this code.

Thanks,

Anita Jeyan.

Re: Poor performance in VBFA select statement

former_member585060 — Sun, 29 Apr 2012 13:50:05 GMT

Hi,

Just check any secondary index available for the VBFA table, i think you use standard secondary index 'M01' with VBELN and POSNN fields, just check the Status of the Index, if it 'New' you can't use it, if it is 'Active' you can use it. If not 'M01' try with 'Z02' which has fields VBELN and VBTYP_N. Change your I_VBFA table structure to use secondary index.

Ex code with Secondary Index 'Z02':-

TYPES : BEGIN OF ty_vbfa,

vbeln TYPE vbfa-vbeln, " Modified structure to use secondary index

vbtyp_n TYPE vbfa-vbtyp_n,

vbelv TYPE vbfa-vbelv,

posnv TYPE vbfa-posnv,

posnn TYPE vbfa-posnn,

vbtyp_v TYPE vbfa-vbtyp_v,

END OF ty_vbfa.

DATA : i_vbfa TYPE STANDARD TABLE OF ty_vbfa,

wa_vbfa TYPE ty_vbfa.

SELECT vbeln

vbtyp_n

vbelv

posnv

posnn

vbtyp_v

FROM vbfa

INTO TABLE i_vbfa

FOR ALL ENTRIES IN is_bil_invoice-it_gen

WHERE vbeln = is_bil_invoice-it_gen-bil_number

AND vbtyp_n IN ('M', 'N')

AND posnn = is_bil_invoice-it_gen-itm_number.

Try with above code and see the performance, modify the I_VBFA structure according to secondary index.

Thanks & Regards

Bala Krishna

Re: Poor performance in VBFA select statement

ThomasZloch — Sun, 29 Apr 2012 13:50:30 GMT

VBFA access has been discussed many times before, please search for available information and read SAP note 185530 as a start.

Thread locked.

Thomas