Querying VBFA table - time varied

[former_member199632]

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

[former_member1275497]

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

[eduardo_hinojosa]

Hi

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

[yuri_ziryukin]

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.