Skip to Content
avatar image
Former Member

Join Performance in Detail

Hi all,

I have a special question for the performance of the join statement. Can anyone explain me how the join really works. If I have a SELECT Statement like the following:

SELECT MARA~MATNR

MARC~WEKRS

FROM MARA JOIN MARC

ON (MARAMATNR = MARCMATNR)

WHERE MARC~MMSTA = 01

ORDER BY MARC~WERKS.

In my example MARC has 1.000.000 entries and MARA has 200.000 . Can someone explain which is done first? I think first he made the join means, he creates a temp table with 1.000.000 entries, when delete all that do not match the SELECT Clause and than make the ORDER BY. But I am not really sure, so maybe someone can help me.

Thanks,

Chris

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • Aug 12, 2004 at 10:23 AM

    Make a report with your select and check the time elapsed, then repeat changing the order of your tables in the FROM section, it will answer your question.

    IMO, it will depend of your DB server. But with the few things I learned about SAP and ABAP, I think it will be faster (more memory needed, but) using internal tables.

    Fill your itab_marc first, filtering the 1000k registers and loop it. If you do the insertions in your itabs using ORDER BY clause SQL, the result will be fast.

    Maybe it doesn't help, but if you do it, post the results 😊

    Regards,

    Vic

    -


    SELECT *

    FROM MARC

    INTO TABLE it_marc

    WHERE MMSTA = 01

    ORDER BY MATNR.

    SELECT *

    FROM MARA

    INTO TABLE it_mara

    ORDER BY MATNR.

    loop it_marc.

    loop it_mara.

    if it_mara-matnr = it_marc-matnr.

    ...

    endif.

    endloop.

    endloop.

    -


    My doubt: if you use nested loops... the nested one will be restarted for each iteration of the first one??

    If it is, you can improve the speed making a delete in it_mara where matnr < it_marc-matnr.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 11, 2004 at 05:02 AM

    If performance is really an issue, I think the ORDER BY clause can be removed, as you do not want the database to take extra time in sorting the records which can be done at the application using SORT thus reducing database server time.

    According to my understanding the join query should not bother you, since database will tweak the SQL query you put forth and irrespective of the order you put(that is first MARA or MARC), will give the optimum performance.

    Not sure about this point, but have a gut feeling that this is the way it works.

    Subramanian V.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks for your help. I think you are right with yor recommendation to the ORDER BY Clause.

      But I really wants to know, how the join works, becuase I am working in a really big environment (some million entries in most of the tables) and performance is everytime a hot topic.

      I think it should also depends on the database we use. In our case it is Oracle 8.0.6!

      Would be nice if someone can get deeper into it, so that I really understand how to build a optimal join select.

      Thanks,

      Chris

  • avatar image
    Former Member
    Aug 12, 2004 at 02:54 AM

    Hello Chris,

    In a JOIN statement, the precedence of tables evaluated is determined by the selection criteria, ie. the contents of the WHERE clause.

    In your example:

    > SELECT MARA~MATNR

    > MARC~WEKRS

    > FROM MARA JOIN MARC

    > ON (MARAMATNR = MARCMATNR)

    > WHERE MARC~MMSTA = 01

    > ORDER BY MARC~WERKS.

    Since you are only specifying MARC-MMSTA in the selection criteria, this will be used as the starting point of the data selection process. The DB will first evaluate the MARC table entries to satisfy your criteria of MMSTA = 01, then it will do the inner join to MARA using MATNR.

    The sequence of the join, MARA join MARC or MARC join MARA, does not make a difference.

    Generally, in a SELECT statement, the part that makes the biggest impact on performance is always the WHERE and ON clauses, as these are used by the DB optimizer to determine which index to use in which database table.

    Have a nice day!

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 12, 2004 at 08:20 AM

    Hello Chris,

    if you want performance you'll need an index on MARC:

    non unique with fields MANDT, MMSTA

    (SE11)

    Viktor

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 12, 2004 at 08:28 AM

    Chris, do the following:

    - create a temporary program containing only this select;

    - Start SQL Trace (ST05) - turn on the trace;

    - execute the program created on the previous step;

    - stop the trace;

    - examine the trace (in the list of database operations, select your query and push "Explain SQL"): the execution plan will show you your query, in the exact form that the DB Server executes it, after tweaking and all. It will also tell you if your query uses any available index or not (if the DB Server makes a sequential scan of the tables, an index search etc.)

    Add comment
    10|10000 characters needed characters exceeded