Skip to Content
avatar image
Former Member

Regarding the perfomance of a join .

Hi,

I have a join with tables MARD,MVKE,MAKT,MARA .

Is it a bad habit to join more than 3 tables , is it inversely affect the perfomance.

SELECT dmatnr AS material dwerks AS plant dlgort AS slocation dumlme dlabst rmeins k~maktx AS descr

INTO CORRESPONDING FIELDS OF TABLE it_data2 FROM mard AS d INNER JOIN mvke AS m

ON dmatnr = mmatnr

INNER JOIN mara AS r

ON rmatnr = dmatnr

INNER JOIN makt AS k

ON kmatnr = dmatnr

WHERE

d~matnr IN r_matnr

AND d~werks IN r_plant

AND m~vkorg IN r_sales.

Thanks & Regards,

Ratheesh BS

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Oct 16, 2008 at 12:49 PM

    Not a bad habit. If done carefully in respect to the key relationships between the tables, a join can perform very well.

    Did you run this code already? Is there a specific problem you have with it?

    The only thing I see at quick glance is that you probably want to include MAKT-SPRAS in the ON-conditions to avoid seeing duplicate rows in multiple languages.

    Thomas

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 16, 2008 at 12:58 PM

    Hi ,

    Lot of Inner joins inversely effect the performance as well as does not consider a good practice.

    U can take results in internal tables then perform operations like for all corresponding rows etc to get data and improve performance.

    Thanks,

    Smita

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 16, 2008 at 03:47 PM

    Hi,

    Using inner joins more than once adversly affects the performance. Instead you can fetch the data into internal tables and then using parallel cursors looping at internal tables will improve the performance of the program.

    Regards,

    Sumalatha N.

    Add comment
    10|10000 characters needed characters exceeded

    • So you (and the other responder) seriously think that hitting the database several times instead of once, sucking a lot of redundant data into memory and juggling with multiple internal tables will increase the performance?

      I am always open to learn better, please let me know any proof, sources or own experience backing such statements.

      Thomas

  • avatar image
    Former Member
    Oct 16, 2008 at 07:25 PM

    I have tried to re-arrange your join statement. Let me know if this helps. It all really depends on which of your ranges / select-options you intend to make mandatory. You need to make some of your range / select-options mandatory.

    SELECT d~matnr AS material
           d~werks AS plant
           d~lgort AS slocation
           d~umlme
           d~labst
           r~meins
           k~maktx AS descr
    INTO CORRESPONDING FIELDS OF TABLE it_data2
    FROM       mara AS r
    INNER JOIN makt AS k ON r~matnr = k~matnr
    INNER JOIN mvke AS m ON m~matnr = r~matnr
    INNER JOIN mard AS d ON d~matnr = r~matnr
    WHERE r~matnr IN r_matnr
    AND   d~werks IN r_plant
    AND   m~vkorg IN r_sales
    AND   k~spras EQ sy-langu.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 17, 2008 at 11:57 AM

    Hi,

    Nothing like that......

    You can keep of using the same......Infact if used properly it can give you better performance

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 25, 2008 at 06:43 PM

    HI,

    Your code contains a join on more than 2 tables thats why it degrade your performance. Try to break the join and use the FOR ALL ENTRIES IN Statement.

    Also INTO CORRESPONDING FIELDS OF . Please remove this statement from the query.

    Thanks,

    Chidanand

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Chidanand - SAP implements views using JOINs. Can you explain why there are many standard SAP views over far more than two tables??

      Rob

  • Oct 27, 2008 at 08:32 AM

    > Your code contains a join on more than 2 tables thats why it degrade your performance. Try to break the join and use

    > the FOR ALL ENTRIES IN Statement.

    on which experience do you base this comment ?????

    > Also INTO CORRESPONDING FIELDS OF

    and that

    Please add arguments which it should be that way.

    > Please remove this statement from the query.

    if not, then better write nothing.

    Siegfried

    Add comment
    10|10000 characters needed characters exceeded