Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Need pointers to improve performance of a select query to table vbrk

Hey Folks,

I have a query , whose performance needs to be tuned , as such:

    SELECT a~vbeln
           a~fkart                     
           a~waerk
           a~fkdat                    
           b~posnr
           b~vgbel
           b~vgpos
           b~matnr
           b~arktx
           b~prctr
           b~txjcd
      INTO TABLE gi_billing_items
      FROM vbrk AS a
      INNER JOIN vbrp AS b
      ON a~vbeln = b~vbeln
      FOR ALL ENTRIES IN gi_sales_items
     WHERE b~vgbel = gi_sales_items-vbeln
       AND b~vgpos  = gi_sales_items-posnr
       AND b~matnr  = gi_sales_items-matnr
       AND b~werks  = gi_sales_items-werks.

where

gi_sales_items is an internal table consisting of 278 entries,.

The result set collected in table gi_billing_items is 200 records

The total execution time for this query for the afore given data is 72,983 ms with the average time/record being ~ 9,471 ms which is too high.

When I try to verify the Explain Plan of the query in ST05, in the Access path I see that the performance of Query Block 1 is bad. Query Block 1 is of the QBLOCK_TYPE UNIONA. Its the very first step in the Query execution internally.

The indexes are defined on participating tables VBRK and VBRP as:

VBRK~0 MANDT,VBELN

VBRK~LOC MANDT,LCNUM

VBRP~0 MANDT,VBELN,POSNR

VBRP~Z01 FPLNR,MANDT

VBRP~Z02 MANDT,MATNR,WERKS

Its clear from the ST05, STAD and SE30 traces that there is a performance issue in this query. Does anyone have any pointers as to how to resolve this issue? Is there a protocol one needs to follow when using the "FOR ALL ENTRIES IN" clause? Or is there a need for any secondary indexes to be created?

Please let me know

Thanks and Best Regards,

Rashmi.

Former Member
Former Member replied

Hi,

Try using the VBFA...to get the Invoice number and line item..and then use that value in VBRK...

* Declare the internal table for T_VBFA.

IF NOT gi_sales_items[] IS INITIAL.
SELECT VBELV
              POSNV
              VBELN
              POSNN
              VBTYP_N
              INTO TABLE T_VBFA
              FOR ALL ENTRIES IN gi_sales_items
              WHERE VBELV = gi_sales_items-VBELN
              AND       POSNV = gi_sales_items-POSNR
              AND       VBTYP_N = 'M'.             "Invoice                       ""Added this..

ENDIF.

**Add two columns to GI_SALES_ITEMS..to store the VBELN POSNN the data from t_vbfa..let's assume it is VBELN_VF and POSNR_VF

* Basically merge gi_sales_items AND t_vbfa

** Then use that field in 
IF NOT GI_SALES_ITEMS[] IS INITIAL.

      SELECT a~vbeln
           a~fkart                     
           a~waerk
           a~fkdat                    
           b~posnr
           b~vgbel
           b~vgpos
           b~matnr
           b~arktx
           b~prctr
           b~txjcd
      INTO TABLE gi_billing_items
      FROM vbrk AS a
      INNER JOIN vbrp AS b
      ON a~vbeln = b~vbeln
      FOR ALL ENTRIES IN gi_sales_items
      WHERE b~vbeln = gi_sales_items-vbeln_vf   " Change here
       AND b~posnr  = gi_sales_items-posnr_vf     " Change here
       AND b~matnr  = gi_sales_items-matnr
       AND b~werks  = gi_sales_items-werks.


ENDIF.

Thanks

Naren

Edited by: Narendran Muthukumaran on Oct 15, 2008 11:35 PM

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question