Join Optimization

[Former Member]

So my task has been to join several tables together so that the accountants can check account assignments.  They wanted several fields from a total of 6 tables.  It is difficult to do this because of needing the account assignment description and the valuation class so that nothing is left out when joining these tables.  The code below works, but is getting a great deal of information because they want to be able to run it wide open and sort it after using the ALV Grid.  The only filter on the join that is mandatory is the language, but obviously running this without anything else takes a long time.  I have debugged this and I know that the LOOP AT statements are what is causing this to run so slow.  I'm trying to figure out a way to optimize this, maybe with a SELECT...ENDSELECT loop and do it all at once, but I am pretty new to ABAP and am having trouble getting started.  If anyone could give assistance that would be great!  Thanks!

*Put data into internal table (Plant, Mat No, Mat Type, Mat Stat, Mat Desc, Valuation Class, Procurement Type, Sales Org, Dist Ch, Div ID, AAG, and AAG Desc

   SELECT mvke~matnr mvke~vkorg mvke~vtweg mvke~ktgrm mara~mtart mara~spart makt~maktx marc~werks marc~mmsta marc~beskz marc~sobsl

     FROM ( mvke

          INNER JOIN mara

          ON mara~matnr = mvke~matnr

          INNER JOIN makt

          ON makt~matnr = mvke~matnr

          INNER JOIN  marc

          ON marc~matnr = mvke~matnr )

     INTO CORRESPONDING FIELDS OF TABLE dt_materials

       WHERE marc~werks IN werks

       AND makt~spras = spras

       AND mvke~ktgrm IN ktgrm

       AND mvke~matnr IN matnr

       AND mvke~vkorg IN vkorg

       AND mvke~vtweg IN vtweg.

* Gets Account Assignment Group Description

   LOOP AT dt_materials INTO ds_materials.

     tab_index = sy-tabix.

     SELECT SINGLE tvkmt~vtext

       INTO ds_materials-vtext

       FROM tvkmt

       WHERE tvkmt~ktgrm = ds_materials-ktgrm

       AND tvkmt~spras = spras.

     SELECT SINGLE bklas

       INTO ds_materials-bklas

       FROM mbew

       WHERE matnr = ds_materials-matnr

       AND   bwkey = ds_materials-werks.

     IF sy-subrc EQ 0.

       MODIFY dt_materials FROM ds_materials INDEX tab_index TRANSPORTING vtext bklas.

     ENDIF.

   ENDLOOP.

[ThomasZloch]

Joining MARC and MVKE can be problematic. Plant view and sales view, both have only MATNR in common. Suppose you have three plants and four sales views for a MATNR, this would give you twelve rows in the result. Much more, if more plants and sales views are involved.

How do you or your accountants expect this to look like in the list?

Thomas

[Former Member]

First get the  MARA, MARC and MBEW  based on Material and Plant,

1. Don’t overload the data base, try to use ABAP space for  manipulate the data.
2. Don’t use CORRESPONDING FIELDS OF, it will affect the performance
3. For Material description, get it in a separate internal table and whenever required, just use read.
4. For Acc text, read all the data from table TVKMT and keep it in a separate table and read the data whenever required.
5. Based on MARA, MARC and MBEW output, read the data from table MVKE (using the KEY input of MATNR, VKORG and VTWEG)
6. Now you have all that data in your MVKE internal table and delete the data from internal table with vsalue of ktgrm.
7. Loop the MVKE internal table and manipulate it and  finally move to final internal table
8. Try to use sort internal table,

[Former Member]

We have told them that they might not necessarily get the material if it doesn't have a sales view and they seem to have stated that it's not really an issue, although I'd like to do this the right way.

[Former Member]

My opinion!

- Inner join on MARA,MVKE and MARC

- get the texts by using for all entries and withe key fields (it_tvmkt, it_maktx)

* if you use the filed symbols not required to write the modify statement

LOOP AT dt_materials assigning <ds_materials>.

* read the table it_makt

if sy-subrc = 0.

<ds_materials>-maktx = wa_makt-maktx.

endif.

same way for tvmvkt.

end loop.

[former_member187748]

Hi Ashley,

do it something like this, and see is your code get optimize or not

SELECT mvke~matnr mvke~vkorg mvke~vtweg mvke~ktgrm mara~mtart mara~spart makt~maktx marc~werks marc~mmsta marc~beskz marc~sobsl

INTO CORRESPONDING FIELDS OF TABLE dt_materials

     FROM (  (  ( mvke

          INNER JOIN mara ON  mvke~matnr = mara~matnr )

          INNER JOIN makt ON mvke~matnr  = makt~matnr )

          INNER JOIN  marc ON mvke~matnr = marc~matnr )

        WHERE marc~werks IN werks

       AND makt~spras = '  '                  ----------please pass the language here

       AND mvke~ktgrm IN ktgrm

       AND mvke~matnr IN matnr

       AND mvke~vkorg IN vkorg

       AND mvke~vtweg IN vtweg.

LOOP AT dt_materials INTO ds_materials.

     tab_index = sy-tabix.

     SELECT SINGLE tvkmt~vtext

       INTO ds_materials-vtext

       FROM tvkmt

       WHERE tvkmt~ktgrm = ds_materials-ktgrm

       AND tvkmt~spras = ' '.                                             -> put the language here also

[Former Member]

The best sol. for your problem is to fetch the data from different database tables in different internal tables based on your selection data. And finally put them into a final internal table.

Pl. don`t use select statement inside the Loop.

Cheers !!!

Vivek