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

[Former Member]

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]

If you think gi_sales_items-matnr should always have a material number, you should work table VAPMA into the join and select the materila from there.

Rob

[Former Member]

Can you try with this?

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 vbrp as b

inner join vbrk as a

ON a_{vbeln = b}vbeln

FOR ALL ENTRIES IN gi_sales_items

WHERE b~matnr = gi_sales_items-matnr

AND b~werks = gi_sales_items-werks

and b~vgbel = gi_sales_items-vbeln

AND b~vgpos = gi_sales_items-posnr.

Regards,

Carlos

[Former Member]

@Carlos, thanks for the alternative, I did try it. But unfortunately the performance seemed to deteriorate further with this option

@Rob, I cannot do away with the tables VBRK and VBRP since I need to fetch the Billing Header and Billing item details on the basis of the sales items. I guess I did a mistake by not giving the definition of the table gi_sales_items. Is there another means by which VAPMA can be used in this query?

The table gi_sales_items has the definition as follows:

       BEGIN OF gt_sales_items,
         vbeln TYPE vbak-vbeln,
         erdat TYPE vbak-erdat,
         auart TYPE vbak-auart,
         vkorg TYPE vbak-vkorg,
         bstnk TYPE vbak-bstnk,
         bukrs TYPE vbak-bukrs_vf,
         posnr TYPE vbap-posnr,
         matnr TYPE vbap-matnr,
         pstyv TYPE vbap-pstyv,
         werks TYPE vbap-werks,
         kunnr TYPE vbpa-kunnr,
         regio TYPE kna1-regio,
       END OF gt_sales_items.

@Rob, Since in this case, a material number will always be there, I did try incorporating VAPMA in the query where table VBRK was joined to VAPMA on the matnr and the whereclause used the matnr of table VAPMA. The performance was even more hit due to this. Please let me know if my usage of the same is appropriate or is there an alternate way to make use of this table in the query.

Besides, I am not being able to get to the bottom of why this query is non performant. And what is it about the query block type UNIONA which is rendering the query non performant.

Edited by: Rashmi B on Oct 15, 2008 9:17 PM

[Former Member]

I'm not suggesting you do away with the other tables. Just add a new JOIN condition on VAPMA and in the WHERE, use this table for the material.

This works. I've done it before and this is what this table is used for.

Rob

[Former Member]

@Rob,

The time taken for the query execution is : 15,583,864 ms now ant the average time/record is 577,180 ms.

I tried changing the query to :

    SELECT a~vbeln
           a~fkart                    
           a~waerk
           a~fkdat                     
           b~posnr
           b~vgbel
           b~vgpos
           c~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
      inner join VAPMA as c
      ON a~VBELN = c~VBELN
      FOR ALL ENTRIES IN gi_sales_items
     WHERE b~vgbel = gi_sales_items-vbeln
       AND b~vgpos  = gi_sales_items-posnr
       AND c~matnr  = gi_sales_items-matnr
       AND b~werks  = gi_sales_items-werks.

Is this how you suggested. Please correct me if I am wrong

[Former Member]

Well, you have to include the item. See if this is better:

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
      INNER JOIN vapma AS c
        ON b~vbeln = c~vbeln AND
           b~posnr = c~posnr
      FOR ALL ENTRIES IN gi_sales_items
     WHERE b~vgbel  = gi_sales_items-vbeln
       AND b~vgpos  = gi_sales_items-posnr
       AND c~matnr  = gi_sales_items-matnr
       AND b~werks  = gi_sales_items-werks.

Rob

[Former Member]

Thanks Rob, unfortunately this increased the processing time to 21,069,518 ms.

Oh.. Woe is me!

[Former Member]

How many entries in the two internal tables?

Rob

[Former Member]

Check for the indexes in all the participating tables

[Former Member]

278 items in Sales table and 200 items in the billing table

[Former Member]

Naren's solution may work, but I still think that it should be quicker and easier to use VAPMA.

Rather than use the runtime analysis, I think it would be better to use ST05 and look at the EXPLAIN. The runtime analysis gets fooled by buffering. You have to run it multiple times to get a good picture of what is happening.

The EXPLAIN is the best way to compare different SELECTs.

Rob

[Former Member]

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

[Former Member]

1) You can use pre-select from VBFA as it was suggested already but if you need to find all billing docs and not just invoices - you'll need to add other VBTYP to the following check :

AND VBTYP_N = 'M'. "Invoice

2) You can just add one more index to VBRP by MANDT, VGBEL, VGPOS and it will help

[Former Member]

Hi Rashmi,

Try the following piece of code.

IF NOT gi_billing_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 vbfa AS c
    INNER JOIN vbrp AS b
    ON  c~vbeln = b~vbeln
    AND c~posnn = b~posnr
    INNER JOIN vbrk AS a
    ON  a~vbeln = b~vbeln
    FOR ALL ENTRIES IN gi_sales_items
    WHERE c~vbelv = gi_sales_items-vbeln
    AND   c~posnv = gi_sales_items-posnr
    AND   vbtyp_n = 'M'.

ENDIF.

[Former Member]

@Rob: I did check the query execution times in the Explain Plan of the SQL trace in ST05. The first Query Block was being projected as non performant. Am not sure if I had modeled the query appropriately.

@Narendran : This solved my issue...I had to tweak it a bit, but the idea of fetching the keys from VBFA and using it did the trick. Thanks a lot

@Mark: Thanks for the solution. This was ultimately how I modeled the query.