Need pointers to improve performance of a select query to table vbrk
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.
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:
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,
Narendran Muthukumaran replied
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.
Edited by: Narendran Muthukumaran on Oct 15, 2008 11:35 PM