Select Query takes too long time

[sorin_t]

Hello,
I made a selection query from table AFRU but it is taking too long time to execute.
What can I do to optimize it?
This is the query:
SELECT a~werks a~aufnr a~budat a~vornr a~grund a~zzcurrency a~stokz SUM( a~xmnga ) AS xmnga SUM( a~zzscrapvalue) AS zzscrapvalue SUM( a~lmnga ) AS lmnga b~lead_aufnr b~plnbez c~matnr d~arbpl e~steus e~ltxa1

FROM afru AS a

INNER JOIN afko AS b ON b~aufnr = a~aufnr
INNER JOIN afpo AS c ON c~aufnr = b~aufnr
INNER JOIN s022 AS d ON d~aufnr = b~aufnr AND d~werks = p_werks AND d~matnr = b~plnbez AND d~posnr = c~posnr
INNER JOIN afvc AS e ON e~aufpl = b~aufpl AND e~plnfl = d~plnfl AND e~vornr = d~vornr
INTO CORRESPONDING FIELDS OF TABLE gt_dummy
WHERE a~werks = p_werks
AND a~ersda = p_ersda
GROUP BY a~werks a~aufnr a~budat a~vornr a~grund a~zzcurrency a~stokz b~lead_aufnr b~plnbez c~matnr d~arbpl e~steus e~ltxa1.
I can't put another selection parameter because the query must work with only plant and confirmation entry date.
I did a test with a confirmation date that had 15.000+ records in table AFRU and the query took 20+ min to execute..

Thank you for your help!
Alex

[michael_piesche]

Before you start with the following recommendations 1. and 2., I would recommend to check, which select or join takes the most time. So you would at first only select from AFRU, than add to your join AFKO and so on and comparing the time that has passed for each select.

I am assuming, that AFRU and S022 are the ones taking the most time, depending on whether Index AFRU~E1 is not activated or there is not a similar one, otherwise it might be 'just' S022 that has a missing index for your join statement. You can also do a performance trace for your SQL statement with ST05 and ST01 and see whether the select for the different tables is either done sequantially or by index.

With ST04 you can create SQL statements to test your selects in productive system without having to transport different versions of your code.

Here is what you most likely have to do, to fix your performance issue:

1. Check whether index AFRU~E1 is active (it has fields ERSDA, WERKS), if not, get it activated, otherwise your selection on AFRU will have sequential read and not an index read!
   In your where statement, put ERSDA before WERKS (it shouldnt make a difference, as the DBMS should know what to do, but it makes it more clear when reading the code, that you expect to be an index on those)
2. Most likely you will also need a new non-standard Index for S022 with the fields WERKS, MATNR, AUFNR, POSNR (or at least for instance WERKS, AUFNR), and change the inner join by attributes to the order of the index (also just to visualize that you expect an index)
3. For the other tables, the join statements are already using at least the first part of the primary index or more, so those should not be of concern right now

If activating or creating is not an option, the only thing left for you is, to look for other ways to select and join the tables, that will utilize primary or secondary table indexes. Otherwise, you are out of luck.

[sorin_t]

Thank you for help! I made a index for table AFRU on WERKS and ERSDA and it reduced the time to 20 seconds.

[Sandra_Rossi]

Please provide the "execution plan" (via performance trace ST05 for instance) so that we can help you interpret it.

[michael_piesche]

sorin_t, 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