topic Re: Querying VBFA table - time varied in Application Development and Automation Discussions

Querying VBFA table - time varied

former_member199632 — Tue, 24 Apr 2012 06:39:22 GMT

Hello,

QUERY:1

select * from vbfa

where vbeln='0000565145' and vbtyp_n='r' and vbtyp_v='J'

There are 9M records in the VBFA table.

There is an index on these columns. (MANDT,VBELN,POSNN)

I have executed above query on peek time where high amount of users are there in PRD

(around 300 users)

It took 35 seconds

When there are less users (around 50), i have executed this query.

It took 9 seconds

1.) What would be the reason for this?

How can i improve the peek time performance of this query.

---- AND ----

QUERY:2

select * from vbfa where vbelv='0200000011'

i have executed this query. (QUERY 2)

when i executed directly , though SQL it took around 12 seconds

when executed through se16 , it took around <1 second (see the attached image)

2.) Why there is such time difference. How to improve this type of a query

Kindly guide...

regards,

Zerandib

Re: Querying VBFA table - time varied

former_member1275497 — Tue, 24 Apr 2012 08:22:08 GMT

Hi,

Please use open cursor for select statement.

Eg :

data : c1 type cursor.

Open cursor with hold c1 for

select * from vbfa

where vbeln='0000565145'

and vbtyp_n='r'

and vbtyp_v='J'.

Do.

FETCH NEXT CURSOR c1 INTO TABLE it1

PACKAGE SIZE 10000.

IF sy-subrc = 0.

APPEND LINES OF it1 TO it.

REFRESH it1.

ELSE.

CLOSE CURSOR c1.

EXIT.

ENDIF.

Enddo.

Please check it and confirm.

regards

sandeep

Re: Querying VBFA table - time varied

eduardo_hinojosa — Tue, 08 May 2012 09:26:30 GMT

See note 185530 (Performance: customer developments in SD) and take in account the suggestions related with the use of table VBFA.

I hope this helps you

Regards

Eduardo

Re: Querying VBFA table - time varied

yuri_ziryukin — Wed, 09 May 2012 15:38:07 GMT

This was the only correct answer.

The problem is that the primary key of VBFA has fields VBELV and POSNV before fields VBELN and POSNN. So the primary index is not effectively used during your selection. And in standard there are no further indexes on VBFA.