How to improve this query's performance?

[Former Member]

Hello,

I have the following query that can take 5+ mins to run in our QNA system.  This is unacceptable for our users (generally nothing should take longer than 1 min to run as this query is used in a RFC for a web application).  I have worked with our DBA to add indexes to the VBPA-LIFNR field as well as the VBAK-ANGDT and BNDDT fields.  Neither of these changes has caused a significant improvement in performance.

Any ideas on how to perform this query in a quicker way would be much appreciated.

The purpose of this query is to grab the sales quotes (along with some additional data) that are still valid, open, and unshipped for a particular carrier.

"select for partner table with default entry 000000

     SELECT A~VBELN AS RELEASE_NUMBER A~POSNR AS ITEM_NUMBER A~WERKS

      A~ARKTX AS MATERIAL_DESC A~KWMENG AS QUANTITY A~VRKME AS UNIT A~ZZAGRIUMPO AS PO_NUMBER B~LIFNR

      INTO CORRESPONDING FIELDS OF TABLE ITAB_RELEASE

      FROM VBAP AS A INNER JOIN VBPA AS B ON ( B~VBELN = A~VBELN AND B~POSNR = 000000

      AND B~LIFNR = P_CNUMBER AND B~PARVW = 'C1' )

      INNER JOIN VBAK AS C ON C~VBELN = A~VBELN

      INNER JOIN VBUP AS D ON D~VBELN = A~VBELN AND D~POSNR = A~POSNR

      WHERE A~ABGRU = ''

      AND ( C~ANGDT <= SY-DATUM

      AND   C~BNDDT >= SY-DATUM )

      AND   D~GBSTA = 'A'.

Thanks,

Mike

[Former Member]

I'm thinking that you should reorganize your join.  You are looking to visit the table with the conditions that will most limit your data set first.  Perhaps to start at VBAK then VBAP.  The sy-datum limitations seem like they could be fairly stringent.

Neal

[Former Member]

The query has been reogranized into two separate queries as show below.  The second query is nearly instant, but the first part is running in about 4 mins compared to the original which runs in about 6.4 mins.  I'm happy with the gain thus far, so now on to improving the performance of the first part.  Thank you all for your help.  I will work with my DBA to solve the remaining speed issues.

    "first select all valid unprocessed releases
    SELECT B~VBELN B~POSNR INTO CORRESPONDING FIELDS OF TABLE ITAB_RELEASES
      FROM VBAK AS A
      INNER JOIN VBUP AS B
      ON A~VBELN = B~VBELN
      WHERE ( A~ANGDT <= SY-DATUM
      AND   A~BNDDT >= SY-DATUM )
      AND ( A~AUART = 'ZCRL'
      OR A~AUART = 'ZTRA'
      OR A~AUART = 'ZTER' )
      AND B~GBSTA = 'A'.

    "now using the list of valid/unprocessed releases, grab details for all non-rejected releases
    SELECT A~VBELN AS RELEASE_NUMBER A~POSNR AS ITEM_NUMBER A~WERKS
    A~ARKTX AS MATERIAL_DESC A~KWMENG AS QUANTITY A~VRKME AS UNIT A~ZZAGRIUMPO AS PO_NUMBER B~LIFNR
    INTO CORRESPONDING FIELDS OF TABLE ITAB_RELEASE
    FROM VBAP AS A INNER JOIN VBPA AS B ON  A~VBELN = B~VBELN AND B~POSNR = 000000
    AND B~LIFNR = P_CNUMBER AND B~PARVW = 'C1'
    FOR ALL ENTRIES IN ITAB_RELEASES
    WHERE A~VBELN = ITAB_RELEASES-VBELN
    AND   A~POSNR = ITAB_RELEASES-POSNR.

[matt]

When you've resolved the first part, that should give you a pointer on how to recombine the second part into the INNER JOIN.

Don't forget to add a check that ITAB_RELEASE is not empty.

Also, use A~AUART IN ('ZCRL', 'ZTRA', 'ZTER') rather than those ORs.

[Former Member]

Thanks for the tips Matthew.  Much appreciated.  I'm new to ABAP (coming from 9yrs of JEE) and any pointers are appreciated.

Mike

[Former Member]

I remember something about OR's being problematic.

A~AUART = 'ZCRL'
OR A~AUART = 'ZTRA'

OR A~AUART = 'ZTER'

Does A~AUART in R_AUART

Improve the situation?

Neal

[matt]

No, it can make it worse. But IN (a,b,c) is fine.

[sakshi_sawhney]

Hi Mike,

You could try splitting above single select query into multiple select queries using for all entries instead of using join.

[Former Member]

Perhaps we should read

http://scn.sap.com/thread/1174072

and the long discussion that follows the post!

Sorry to steal your thunder Matthew!  But it does appear to be THE definitive post...

Neal

[matt]

Beat me to it!

I'll repeat: For All Entries is in nearly every case is less efficient than INNER JOIN. It is also better coding as it is simpler and less prone to error.

In one very large company, I've even written it into the standards - but still it goes on!

Edit - moderator message: Any further posts in this thread that say FAE is better than INNER JOIN will be rejected. Generally it is NOT the case, and should only be used as last option.

[former_member218171]

Hi Michael,

If you have Oracle Enterprise you could use the tunning advisor (you need to install Enterprise Manager), it's a really great tool and has helped me with lots of performance issue.

Hope it helps!

[matt]

What does the explain in ST05 SQL trace give you?

[former_member209120]

This message was moderated.

[Former Member]

This message was moderated.

[former_member209120]

Hi Sagar Dev,

How For all Entries is better than inner join?

Please see this link

http://scn.sap.com/thread/1174072

[Former Member]

This message was moderated.

[Former Member]

Hi Mike,

Instead of B~VBELN = A~VBELN, C~VBELN = A~VBELN & D~VBELN = A~VBELN AND D~POSNR = A~POSNR

Use

A~VBELN = B~VBELN,  A~VBELN = C~VBELN,  A~VBELN =  D~VBELN & A~POSNR = D~POSNR.

and if possible let us know the SQL Trace comments for your select Query.

One more suggestion is try not to use ' INTO Corresposnding Fields of the TABLE' Better to declare a structure with the same fields and then use the same for internal table declaration.

[matt]

Erm....  another myth.

INTO corresponding fields will make little or no difference over INTO TABLE directly. It might save you 0.0001% of the overall execution time. What it will do is make your code less robust and more prone to failure. Relying on the order of fields in a structure is dangerous. INTO CORRESPONDING is much safer.

[former_member209120]

Hi Matthew Billingham,

Many of us having this type of myths, with your suggestions we will come out of that.....

[raymond_giuseppi]

For this myth, read Why "INTO CORRESPONDING" is much better than its reputation created by Thomas Zloch.

Regards,

Raymond

[Sijin_Chandran]

But this SAP Help Portal document :

http://help.sap.com/saphelp_nw70ehp2/helpdata/en/aa/4734970f1c11d295380000e8353423/frameset.htm

Supports what has been mentioned by Balaji Sha .

Just curious to know the real fact , though I am a hard core INTO CORRESPONDING FIELDS user and usually I  don't waste my time ordering the fields in the manner they are present in DB table underneath.

[ThomasZloch]

The text under "Restrict the Number of Columns" does not seem to be fully accurate anymore, as I guess I have proven in the document linked by Raymond. Technology has advanced, but not all SAP Help authors seem to have caught up. This helps fuelling misunderstandings, unfortunately.

Thomas

[Sijin_Chandran]

Fine ,

But the number of fields in DD03L is very less compared to the above case.

So I think there may be some Performance Overhead by  using INTO CORRESPONDING FIELDS in such cases where number of fields are too large , JOINT statement is been used and Volume of Data is also very high.

[Former Member]

It is trivial to prove.  Do the testing...  Calculate the runtime...

Neal

[ThomasZloch]

It should not become too academical, it's about practical relevance to users and system resources, a performance effect <1% isn't worth bothering, I'd even say <20% for stuff like simple reporting. Other factors weigh in like readability, maintainability, robustness of the code etc.

Thomas

[Former Member]

Based on human behavior, You need to gain 4X improvement for the user to actually notice. 

Neal

[ThomasZloch]

...and then they will complain that they cannot hop off anymore for a cup of coffee in the meantime.

[former_member184958]

This message was moderated.

[ThomasZloch]

Your only "hard hitting" criteria seems to be LIFNR = P_CNUMBER, you should not "hide" this in the ON-condition, but rather move it to the WHERE-condition.

Please check if you can incorporate index table VAKPA in your join for quick access to sales documents via partner numbers. The new index you created for VBPA might not be needed.

Also see SAP note 185530 with performance tips for SD customer developments.

Thomas