topic Re: Select in VBFA Table in Application Development and Automation Discussions

Select in VBFA Table

Former Member — Tue, 24 Apr 2007 00:44:40 GMT

Hello All,

We need to improve performance in a program that uses VBFA table in three ways. How can we avoid this table or what we have to do in select statements to improve the program performance.

1) select vbeln posnn vbelv posnv vbtyp_n vbtyp_v

into table it_vbfa1

from vbfa

where vbtyp_n = 'R'

and erdat in r_budat.

2) select vbeln posnn vbelv posnv vbtyp_n

appending table it_vbfa1

from vbfa

for all entries in it_mseg_vbfa_nf

where vbeln = it_mseg_vbfa_nf-vbeln

and posnn = it_mseg_vbfa_nf-posnn

and vbtyp_n = 'R'

3) select vbelv posnv vbeln posnn vbtyp_n

into table it_vbfa2

from vbfa

for all entries in it_vbfa1

where vbelv = it_vbfa1-vbelv

and posnv = it_vbfa1-posnv.

Points will be rewarded!

Thanks,

Michel Khouri

Mars, Inc

Re: Select in VBFA Table

Sougata — Tue, 24 Apr 2007 01:34:41 GMT

2) First check it_mseg_vbfa_nf[] is not initial before executing the 'Select'. This will speed up performance.

3) What's the use of the third select?? I think copying will do the trick and save a lot of runtime -> it_vbfa2[] = it_vbfa1[].

Cheers.

Re: Select in VBFA Table

Former Member — Tue, 24 Apr 2007 01:45:26 GMT

what about create an index for first Open SQL? As vbfa is very big, full table scan will cost a lot of time.

two and three look like no problem, but if itab is very big FOR ALL ENTRIES IN may be not a good solution.

Regards,

-Tim

Re: Select in VBFA Table

Former Member — Tue, 24 Apr 2007 02:54:39 GMT

Michael,

SORT it_mseg_vbfa_nf BY vbeln posnn.

IF NOT it_mseg_vbfa_nf[] IS INITIAL.

select vbeln posnn vbelv posnv vbtyp_n

INTO table it_vbfa1

from vbfa

for all entries in it_mseg_vbfa_nf

where vbeln = it_mseg_vbfa_nf-vbeln

and posnn = it_mseg_vbfa_nf-posnn

and vbtyp_n = 'R'

ENDIF.

Don't forget to reward if useful....

Re: Select in VBFA Table

Former Member — Tue, 24 Apr 2007 11:53:13 GMT

Tim,

Creating an index for this big table will duplicate it on the database? Probably our Basis will not allow us to do this.

Is it possible to use other tables instead of VBFA?

And how can we substitute "for all entries" option in order to optimize performance?

Thanks,

Michel Khouri

Re: Select in VBFA Table

Sougata — Wed, 25 Apr 2007 01:00:10 GMT

Hi,

Have you considered my suggestion above? Try that, I'm sure that'll improve the performance.

Also, it is not possible to replace any other table with VBFA and "FOR ALL ENTRIES" syntax was developed by SAP specifically to handle performance problems resulting from database selects.

Cheers.

Re: Select in VBFA Table

former_member784222 — Wed, 25 Apr 2007 04:51:48 GMT

Hi,

Try to use the following FM to read from VBFA.

SD_VBFA_ARRAY_READ_VBELV

SD_VBFA_READ_WITH_VBELV

SD_VBFA_SELECT

SD_VBFA_SINGLE_READ

SD_VBFA_TRANSFER_TO_BUFFER

Thanks and regards,

S. Chandra Mouli.

Re: Select in VBFA Table

Madhurivs23 — Sun, 16 Sep 2007 09:22:02 GMT

I want to take the delivery docs corresponding to the billing docs from table vbfa.

so i have given selection criteria as

vbfa-vbeln = BILL_DOC_NO

AND vbtyp_v = 'J'

and vbtyp_n = 'M'.

code IS LIKE THIS :

t_bill_list table contains the invoice nos.

code 1)

select distinct

vbelv

vbeln

into table t_bill_del_data

from vbfa

for all entries in t_bill_list

where vbeln eq t_bill_list-vbeln

and vbtyp_v = 'J'

and vbtyp_n = 'M'

the above query was taking time so i changed that to -->

code 2)

loop at t_bill_list into wa_bill_list.

select single

vbelv

vbeln

into wa_bill_del_data

from vbfa

where vbeln eq wa_bill_list-vbeln

and vbtyp_v = 'J'

and vbtyp_n = 'M'.

if sy-subrc = 0.

append wa_bill_del_data to t_bill_del_data.

clear wa_bill_del_data.

endif.

endloop.

but both the codes have poor performance.

Plus the index cannot be created on the table vbfa

please help.

rgds

Madhuri