Performance improvement of a query

[former_member506713]

Hello experts,

i have a very old query which needs to be optimised in ECC. Please help me on this.

SELECT DISTINCT
vbak~vbeln "Sales doc number vbak
vbak~kunnr "Sold-to party vbak
vbak~auart "Sales Document Type vbak
vbap~matnr "Material number vbap
vbap~posnr "Item no in sales order vbap
vbfa~vbeln  "Delivery number
vbfa~posnn "Item no in delivery
vbfa~rfmng "Actual quantity delivered (in sales units)
vbap~kwmeng "Cumulative Order Quantity in Sales Units vbap
vbak~vdatu "requested delivery date vbak
vbkd~bstkd "Customer purchase order number
vbap~abgru "rejection Reason

INTO TABLE gt_so

FROM vbak
JOIN vbap ON vbak~vbeln = vbap~vbeln
JOIN mean ON vbap~matnr = mean~matnr
JOIN vbkd ON vbak~vbeln = vbkd~vbeln
LEFT OUTER JOIN vbfa ON vbfa~vbelv = vbap~vbeln
AND vbfa~posnv = vbap~posnr
AND vbfa~vbtyp_n EQ gc_j

WHERE vbak~auart EQ gv_auart
AND vbak~vbeln IN s_vbeln
AND vbak~erdat IN s_erdat
AND vbak~vdatu IN s_vdatu
AND vbkd~bstkd IN s_bstkd
AND vbap~matnr IN s_matnr
AND mean~ean11 IN s_ean11
AND mean~eantp IN s_eantp.

Cheers,

LLD

[venkateswaran_k]

Is your query is very slow?

Or what is your exact requirement towards performance.

[former_member506713]

Hi, Query is running very slow. How can i improve the performance of the query?

[venkateswaran_k]

Just to understand..

1. Is this a report program? and is your data is huge?

2. What are all the Selection parameter & how many of them are mandatory.

3. If you give date range / sales order range - how much it is taking time?

4. After querying into internal table gt_so - are you processing again in loop?

5. Just put a break point after the query - and see how much time it takes just for query .

Based on this - we can further analyze to improvise.

[Sandra_Rossi]

Please provide the "execution plan" of the query (via SQL trace for instance).

[Sriram2009]

Hi Lalit.

Enable the ST12 trace and check the over all DB & ABAP usage

refer the SAP Note link, How to collect and analyze traces using ST12

https://launchpad.support.sap.com/#/notes/2436955

Regards

SS

[michael_piesche]

1. Your query is joined by primary keys, or at least starting parts of primary key, so that is good, there is not a really better way to join them with a single select and join statement
2. Your Where-restrictions are 'all over the place', which is why you will have bad performance for several use cases, unless if some of the restrictions are mandatory.
   a) If, for instance, VBAK-VBELN is not mandatory, you will need to access by other secondary keys, to achieve an indexed search, and not end up with a sequential search
   b) If, VBAK-ERDAT is mandatory, you need to make sure that VBAK-Index "ERD Index for order entry date" is activated on database (or use AUDAT with Index AUD instead or ERDAT, if possible)
   c) If none, or other restrictions are mandatory, you need to make sure, that all, or those that are mandatory, will be accessing a secondary key on the database tables, most likely you will have to create Extension Indexes for allmost all your restrictions
   d) If you dont have an active index on VBAP~MATNR you would either have to create/active one, or switch the where requirement from "vbap~matnr IN s_matnr" to "mean~matnr IN s_matnr"
3. You should analyse the queries that need "optimization", by using ST05 SQL Trace, activating just before the SQL statement is sent and deactivating right afterwards, view the trace, select the record with the Object Name "VBAK, VBAP, MEAN, VBKD, VBFA" and the "OPEN" operation and display the "Execution Plan" with the "Explain" button, to get a better understanding of how the SQL statement was executed based on the where and join criteria. For more information on how to interpret the execution plan, open the following link and especially look into the provided blogs: https://wiki.scn.sap.com/wiki/display/ABAP/Explain+Plan
4. If for some reason, the join can not be optimized by the SQL interpreter and you continue to end up with sequential reads, you might have to think about creating different select statements for different Where Restrictions, based on what select-options have been used. I assume, forcing the user, to use criteria that lead to indexed values and having the SQL interpreter figure out the necessary optimized join should be sufficient.

Post the execution plan that cause you the most trouble right now. Also let us know, what Database is being used, as this can have an impact on the available functionality for the Execution Plan.

[michael_piesche]

lalit.d, please follow up on your open question.

• comment answers or your question if there are still open issues.
• otherwise mark an answer as accepted if it helped you solve your problem
• or post an answer of yourself and accept it if you found another useful solution yourself
• or redirect your question to another question that is related and was useful to solve your problem
• in the end, close your question