Optimization of SQL query

[Former Member]

Hi experts, can you give me any advice on optimizing the two SQL queries; thanks in advance. This is part of a program which is normally run in background but has to be run in foreground.

1st SQL query:

      SELECT a~mblnr

             a~mjahr

             a~budat

             b~bwart

             b~matnr

             b~werks

             b~menge

             b~meins

        INTO TABLE i_mseg

        FROM mkpf AS a INNER JOIN mseg AS b ON  a~mblnr = b~mblnr

                                            AND a~mjahr = b~mjahr

        FOR ALL ENTRIES IN i_semit_cost

        WHERE a~budat BETWEEN ws_d_first AND ws_d_last

        AND   b~bwart IN r_bwart

        AND   b~matnr = i_semit_cost-matnr_head

        AND   b~werks = i_semit_cost-werks.

2nd SQL query:

      SELECT a~mblnr

             a~mjahr

             a~budat

             b~bwart

             b~matnr

             b~werks

             b~menge

             b~meins

        INTO TABLE i_mseg_ppv

        FROM mkpf AS a INNER JOIN mseg AS b ON  a~mblnr = b~mblnr

                                            AND a~mjahr = b~mjahr

                       INNER JOIN ekko AS c ON  b~ebeln = c~ebeln

        FOR ALL ENTRIES IN i_semit_cost

        WHERE a~budat BETWEEN ws_d_first AND ws_d_last

        AND   b~bwart IN r_bwart_ppv

        AND   b~matnr = i_semit_cost-matnr_head

        AND   b~werks = i_semit_cost-bwkey

        AND   c~reswk = p_werks.

[adam_krawczyk1]

Hi Jack,

If tables that you are using have already millions of records it may be difficult to optimize query such that it can run in the foreground.

Usually it is not recommended to mix JOINs and FOR ALL ENTRIES into same statement due to low performance results. In addition you do join 2 tables in first example and 3 tables in second one. So in fact if you add FOR ALL ENTRIES it is like join on 3 and 4 tables correspondingly and this will be time consuming.

You should try different approaches, do measurements in ST05 database logs and finally choose best performing solution. There are many factors like data distribution and indexes that will influence your query performance.

Some recommendations from me to try:

1. Use only JOINs. Replace FOR ALL ENTRIES IN by another JOIN for i_semit_cost table for first example. It may not be possible if your design has already data selected into i_semit_cost from before.
2. Use only FOR ALL ENTRIES.
   • Try to find which table returns less rows from SELECT and WHERE filtered query. Use it as first SELECT and put results into local table lt_table1.
   • Then do select on next table using FOR ALL ENTRIES IN lt_table1 and put results to lt_table2 etc.
   • With this approach you automatically filter most rows at the beginning so less will be used for further JOINs. In fact with single SELECT and all JOIN statements at once (see point 1) database optimizer should do it automatically, but it does not always need to be right. So it is worth to try results with different tables query sequence.
3. Make sure that fields which you are using in WHERE condition are already covered by index. At least most distinctive values should have the index (index on year will not give you much as there may be thousands/millions of rows for each year, but index on material number will filter results much better).

Regards,

Adam

[matt]

Also note that in most cases INNER JOIN performs better than FOR ALL ENTRIES. So you should try INNER JOIN first.

[yuri_ziryukin]

Hello Jack,

well, there is actually only one ultimate solution for your problem. And this solution is described in the SAP note 1550000. Read it carefully. If you decide to go for it (we've got a couple of very positive feedbacks from our customers), you'll also have to slightly modify the query in order to use new fields from MSEG instead of MKPF.

Regards,

  Yuri