09-03-2013 5:42 PM
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
09-03-2013 6: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
09-03-2013 6: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
09-04-2013 3:17 PM
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.
09-04-2013 3:40 PM
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.
09-04-2013 3:54 PM
Thanks for the tips Matthew. Much appreciated. I'm new to ABAP (coming from 9yrs of JEE) and any pointers are appreciated.
Mike
09-04-2013 4:12 PM
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
09-04-2013 5:37 PM
09-03-2013 7:15 PM
Hi Mike,
You could try splitting above single select query into multiple select queries using for all entries instead of using join.
09-03-2013 7:37 PM
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
09-04-2013 6:05 AM
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.
09-03-2013 7: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!
09-04-2013 6:06 AM
09-04-2013 6:25 AM
09-04-2013 6:37 AM
09-04-2013 6:45 AM
Hi Sagar Dev,
How For all Entries is better than inner join?
Please see this link
09-04-2013 6:59 AM
09-04-2013 7: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.
09-04-2013 8:06 AM
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.
09-04-2013 9:15 AM
Hi Matthew Billingham,
Many of us having this type of myths, with your suggestions we will come out of that.....
09-04-2013 9:59 AM
For this myth, read Why "INTO CORRESPONDING" is much better than its reputation created by Thomas Zloch.
Regards,
Raymond
09-04-2013 10:02 AM
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.
09-04-2013 10:16 AM
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
09-04-2013 10:30 AM
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.
09-04-2013 1:44 PM
It is trivial to prove. Do the testing... Calculate the runtime...
Neal
09-04-2013 1:54 PM
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
09-04-2013 1:57 PM
Based on human behavior, You need to gain 4X improvement for the user to actually notice.
Neal
09-04-2013 2:09 PM
...and then they will complain that they cannot hop off anymore for a cup of coffee in the meantime.
09-04-2013 7:16 AM
09-04-2013 8: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