Skip to Content
author's profile photo Former Member
Former Member

How to improve this query's performance?

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

9 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 03, 2013 at 05:59 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 03, 2013 at 06:40 PM

    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!

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 04, 2013 at 07:27 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 03, 2013 at 06:15 PM

    Hi Mike,

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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • Posted on Sep 04, 2013 at 05:06 AM

    What does the explain in ST05 SQL trace give you?

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 04, 2013 at 05:25 AM

    This message was moderated.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 04, 2013 at 05:37 AM

    This message was moderated.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 04, 2013 at 06:11 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 04, 2013 at 06:16 AM

    This message was moderated.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.