topic Re: Join Optimization in Application Development and Automation Discussions

Join Optimization

Former Member — Thu, 16 Jan 2014 13:22:29 GMT

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.

Re: Join Optimization

ThomasZloch — Thu, 16 Jan 2014 14:26:05 GMT

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

Re: Join Optimization

Former Member — Thu, 16 Jan 2014 18:12:18 GMT

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

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

Re: Join Optimization

Former Member — Thu, 16 Jan 2014 20:11:48 GMT

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.

Re: Join Optimization

Former Member — Fri, 17 Jan 2014 05:31:38 GMT

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.

Re: Join Optimization

former_member187748 — Fri, 17 Jan 2014 05:49:30 GMT

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

Re: Join Optimization

Former Member — Fri, 17 Jan 2014 06:26:11 GMT

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