Join Performance in Detail

[Former Member]

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 (MARA_{MATNR = MARC}MATNR)

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

[VXLozano]

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.

[Former Member]

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.)

[Former Member]

Hello Chris,

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

non unique with fields MANDT, MMSTA

(SE11)

Viktor

[Former Member]

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 (MARA_{MATNR = MARC}MATNR)

> 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!

[Former Member]

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.

[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