Skip to Content
author's profile photo Former Member
Former Member

performance program

hi all, i just want to enhance the performance of program . problem is that there is select statement with three joins and i want to remove that join how can i remove that for example for that following statement.

SELECT mlmatnr mlltkze mkmaktx mameins mantgew magewei

INTO TABLE zw_table

FROM mlgn AS ml

INNER JOIN mara AS ma

ON mamatnr EQ mlmatnr

INNER JOIN makt AS mk

ON mkmatnr EQ mlmatnr

AND mk~spras EQ 'D'

WHERE ml~lgnum EQ '600'

AND ml~lvorm EQ space.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Posted on Aug 13, 2008 at 05:52 AM

    hi,

    use this.

    SELECT matnr ltkze

    FROM mlgn

    INTO CORRESPONDING FIELDS OF TABLE it_mlgn

    WHERE lgnum EQ '600'

    AND lvorm EQ space.

    IF sy-subrc EQ 0.

    SELECT mantr maktx

    FROM makt

    INTO CORRESPONDING FIELDS OF TABLE it_makt

    FOR ALL ENTRIES IN it_mlgn

    WHERE matnr EQ it_mlgn-matnr

    AND spras EQ 'D'.

    IF sy-subrc EQ 0.

    SELECT matnr meins ntgew gewei

    FROM mara

    INTO CORRESPONDING FIELDS OF TABLE it_mara

    FOR ALL ENTRIES IN it_makt

    WHERE matnr EQ it_makt-matnr.

    ENDIF.

    ENDIF.

    LOOP AT it_mlgn.

    MOVE-CORRESPONDING it_mlgn TO zw_table

    LOOP AT it_makt WHERE mantr EQ it_mlgn-matnr.

    MOVE-CORRESPONDING it_makt TO zw_table.

    LOOP AT it_mara WHERE matnr EQ it_makt-matnr.

    MOVE-CORRESPONDING it_mara TO zw_table.

    APPEND zw_table.

    ENDLOOP.

    ENDLOOP.

    ENDLOOP.

    regards,

    Peter

    Edited by: Peter Ruiz on Aug 13, 2008 1:53 PM

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 13, 2008 at 05:57 AM

    Hi,

    The best way to avoid joins is use of FOR ALL ENTRIES IN.

    SELECT matnr ltkze

    FROM mlgn

    INTO CORRESPONDING FIELDS OF TABLE i_mlgn

    WHERE lgnum EQ '600'

    AND lvorm EQ space.

    IF is i_mlgn not initial.

    SELECT mantr maktx

    FROM makt

    INTO CORRESPONDING FIELDS OF TABLE i_makt

    FOR ALL ENTRIES IN i_mlgn

    WHERE matnr EQ it_mlgn-matnr

    AND spras EQ 'D'.

    IF is i_makt not initial.

    SELECT matnr meins ntgew gewei

    FROM mara

    INTO CORRESPONDING FIELDS OF TABLE i_mara

    FOR ALL ENTRIES IN i_makt

    WHERE matnr EQ it_makt-matnr.

    ENDIF.

    ENDIF.

    hope this helps.

    thanx,

    dhanashri.

    Edited by: Dhanashri Pawar on Aug 13, 2008 7:58 AM

    Edited by: Dhanashri Pawar on Aug 13, 2008 8:09 AM

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 13, 2008 at 06:27 AM

    Hello Pankaj,

    Best way is to use FOR ALL ENTRIES.

    In your case...

    MLGN table is having the primary key MATNR and LGNUM.

    MAKT table is having the primary key MATNR and SPRAS.

    MARA is having only one field as key MATNR.

    so first select entries form MARA.

    select matnr

    from mara meins ntgew gewei

    into table g_t_matnr

    where <your condition>.

    Now select entries form MAKT and MLGN for all MATNRs present in internal table g_t_matnr.

    select mlgnmatnr mlgnltkze

    makt~maktx

    into correspodning fields of table g_t_itab

    from ( mlgn inner join makt

    on mlgnmatnr = maktmatnr )

    for all entries in g_t_matnr

    where mlgn~matnr = g_t_matnr-matnr and

    mlgn~lgnum = '600' and

    mlgn~lvorm = ' ' and

    makt~spras = 'D'.

    now it results in good performance.

    You no need to use for all entries again instead of second select statement which contains JOIN operation, because...

    For MLGN you are providing key fields : MATNR using for all entries and LGNUM as '600'.

    For MAKT you are proving key fiels : MATNR using for all entries and SPRAS as 'D'.

    Best Regards

    --

    Sasidhar Reddy Matli.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 13, 2008 at 06:51 AM

    Hi Prasad,

    Use FOR ALL ENTRIES for better performance

    Depends on the requirement. 1 point to remember in case of FOR ALL ENTRIES. You gotto select all the key fields else FOR ALL ENTRIES will delete the duplicate entries.

    if u have more number of tables to join then use inner joins.

    for this requirement using forallentries consume more (memory)space.,and more lines in program also.

    If you want to join DB tables you need to use INNER JOIN and in case of internal tables you can use FOR ALL ENTRIES.

    SELECT matnr ltkze

    FROM mlgn

    INTO CORRESPONDING FIELDS OF TABLE i_mlgn

    WHERE lgnum EQ '600'

    AND lvorm EQ space.

    IF is i_mlgn not initial.

    SELECT mantr maktx

    FROM makt

    INTO CORRESPONDING FIELDS OF TABLE i_makt

    FOR ALL ENTRIES IN i_mlgn

    WHERE matnr EQ it_mlgn-matnr

    AND spras EQ 'D'.

    IF is i_makt not initial.

    SELECT matnr meins ntgew gewei

    FROM mara

    INTO CORRESPONDING FIELDS OF TABLE i_mara

    FOR ALL ENTRIES IN i_makt

    WHERE matnr EQ it_makt-matnr.

    ENDIF.

    ENDIF.

    now it results in good performance.

    regards,

    Ranveer.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 14, 2008 at 08:20 AM

    >

    > hi all, i just want to enhance the performance of program . problem is that there is select statement with three joins and i want to remove that join how can i remove that for example for that following statement.

    Say this 3 times: Joins are not evil

    What you can do in one statement , do it in one Statement - believe me, databases

    are build to retrieve the data as fast as possible: Try not to be smarter then the database optimizer.

    Give ALL your knowledge about the data to the database (table and index statistics,

    index properly, filter out as much as possible)

    Writing compact SQL makes your code slim: Procedural programming is harder to read and to maintain (if you are not the developer that has written the program)

    in your case :

    how much data do you expect ? If you retrieve 3- 4% of the tables data an index access is possible: do the join fields have a proper index ?

    mkspras, mllvorm , ml~lgnum : how selective is your filter (and can it be more selective using additional fields ) ?

    Join ALL the key fields of the involved tables: avoid cartesian products

    >

    > SELECT mlmatnr mlltkze mkmaktx mameins mantgew magewei

    > INTO TABLE zw_table

    > FROM mlgn AS ml

    > INNER JOIN mara AS ma

    > ON mamatnr EQ mlmatnr

    > INNER JOIN makt AS mk

    > ON mkmatnr EQ mlmatnr

    > AND mk~spras EQ 'D'

    > WHERE ml~lgnum EQ '600'

    > AND ml~lvorm EQ space.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 14, 2008 at 08:28 AM

    here we go again, join vs. for all entries...

    The problem with your select is not the join, since the ON-conditions look very OK. The problem is your WHERE-condition on table MLGN, you are not selecting on MATNR, so a full table scan is the result, whether you use joins or for all entries. So leave the join as is and find a way to narrow down the MATNR range.

    Thomas

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.