Problem with JOIN statement

[Former Member]

Hi All,

Does anyone have an idea on what the JOIN statement does below? This is not an inner join, im not sure if it's an outer join because the join type has not been specified.

Thanks in advance.

SELECT head~mblnr

head~budat

item~matnr

item~menge

item~kunnr

item~werks

FROM ( mkpf AS head JOIN mseg AS item

ON head_{mblnr = item}mblnr AND

head_{mjahr = item}mjahr ) JOIN mara AS mat

ON item_{matnr = mat}matnr

INTO TABLE t_goods_issue

WHERE head~budat IN r_date AND

item~werks IN lr_werks AND

item~bwart IN s_bwart AND

item~matnr IN s_matnr AND

mat~mtart IN s_mtart.

-AJ

[JozsefSzikszai]

hi,

if it is not specified, that means it is an INNER JOIN.

You will have as many lines, as many you have in MSEG (MKPF and MARA will be joined to each MSEG lines)

hope this helps

ec

[Former Member]

Hi,

The select query you specified used inner join.

1) First mkpf and mseg is joined based on mblnr and mjahr and afterwards output of join joined with another table mara based on matnr of mara and matnr of mseg.

Thanks,

Naveen Kumar.

[Former Member]

Hi Alex,

In the query for the first join from mkpf and mseg, the entries based on the condition

head_{mblnr = item}mblnr AND

head_{mjahr = item}mjahr ,

All the line items that are matching this condition in both tables will be picked up.

Then from this result, based on this condition

item_{matnr = mat}matnr, all the line items, matching between the preivous result and mara table is fetched into the table t_goods_issue.

suggestion: If you need to use this query in your program, better thing is to write seperate select query for each table, avoid the join and loop at the item table and read the other tables is the best way for good performance.

Regards,

Shobana.K

[JozsefSzikszai]

>

better thing is to write seperate select query for each table, avoid the join and loop at the item table and read the other tables is the best way for good performance.

do you have any proof or just feel so?

[Former Member]

Hi Eric ,

Here is it...

Joins in the FROM Clause

You can read data from more than one database table in a single SELECT statement by using inner or left outer joins in the FROM clause.

The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables. This can considerably increase the amount of data transferred from the database to the application server. Therefore, when you program a join, you should ensure that the SELECT clause contains a list of only the columns that you really need. Furthermore, joins bypass the table buffer and read directly from the database. For this reason, you should use an ABAP Dictionary view instead of a join if you only want to read the data.

The runtime of a join statement is heavily dependent on the database optimizer, especially when it contains more than two database tables. However, joins are nearly always quicker than using nested SELECT statements.

If you dont believe me , this was taken from SAP help.

Finally to reduce direct hit to database table directly many time , it is prefered we dont use the joins.

Regards,

Shobana.K.

[ThomasZloch]

Shobana,

while this quote cannot be ignored, it does not apply to this example. Here, only field BUDAT falls under the redundant 1:N category. If you use the popular FOR ALL ENTRIES approach, you have to redundantly read the key columns from three tables to be able to link the data together later. Also, the involved tables are not buffered, so this is also not a problem here.

Your approach of reading MSEG, MKPF and MARA separately then loop through the MSEG result set and READ TABLE on the other data within the loop (if I understand you correctly) is not OK for this example, since MARA is not used to read data, but only to limit the result set. So you would be reading too many MSEG entries from the database.

Thomas

[JozsefSzikszai]

>

 The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables. This can considerably increase the amount of data transferred from the database to the application server.

This could be true, but this is defintly not the case here with MKPF and MSEG, and not necessarly the case here with MARA (if there are lots of different materials used in the selected material documents).

>

 Therefore, when you program a join, you should ensure that the SELECT clause contains a list of only the columns that you really need.

This is true for any SELECT, not just for JOINs

>

 Furthermore, joins bypass the table buffer and read directly from the database. For this reason, you should use an ABAP Dictionary view instead of a join if you only want to read the data. The runtime of a join statement is heavily dependent on the database optimizer, especially when it contains more than two database tables.

I think the important point is here to analyse and test each single case and based on the runtime results make an intelligent decision at the end. However I still have very good experience with JOINs!

>

 However, joins are nearly always quicker than using nested SELECT statements.

I believe this is far away from truth...

>

  If you dont believe me , this was taken from SAP help.

Can you give a link?

>

  Finally to reduce direct hit to database table directly many time , it is prefered we dont use the joins.

I still use them, hope you don't mind

[ThomasZloch]

Hi Eric,

here is the link

http://help.sap.com/saphelp_nw70/helpdata/en/aa/47349a0f1c11d295380000e8353423/frameset.htm

>The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables.

Not sure if this is still accurate with the latest DB versions...maybe Mr. Boes (he just lives up to his last name or the YukonKid can clarify.

Thomas

P.S. also using JOINS effectively for many years...

[JozsefSzikszai]

Thanks Thomas! The above sentecen I also found in SAPHelp on my own. I would be interested in getting the origin of

However, joins are nearly always quicker than using nested SELECT statements.

This the last sentece from Shobana's post before:

If you dont believe me , this was taken from SAP help.

[ThomasZloch]

> However, joins are nearly always quicker than using nested SELECT statements.

same link, last sentence in chapter "Joins in the FROM Clause".

Or am I missing something?

[JozsefSzikszai]

OK, I got it. Sorry, my fault, I completely misunderstood and was searching for that in 4.7 Help where this sentence is really missing.

[JozsefSzikszai]

>

> >

 However, joins are nearly always quicker than using nested SELECT statements.

>

> I believe this is far away from truth...

I misread, of course JOINs are faster than nested SELECTs, sorry everyone!

[ThomasZloch]

It's Friday afternoon, what can you do...

[Former Member]

Hi Thomas,

Thanks for the information and correcting me.

regards,

Shobana.K

[Former Member]

Hi Thomas,

I ask myself: why should anybody use table joining anymore because it is so inefficient

I'm a Join adict and favor joins (if the statement can expressed with them) over nested SELECTS .

While subqueries (or nested SELECTs) and joins can be very similar in terms of formulating a SQL

statement over several tables, nested SELECTS tend to be come out with more complex queries ,

hence the optimizer has more work to do and probably is exposed to fail in choosing the best execution plan.

Joins can be expressed very easily and if you indexed the join fields well in the involved tables (and some fields that you use as filters in the WHERE) you should outcome with a efficient execution plan.

I see 3 important factors wich plan is choosen by the optimizer:

1 .Sure data volume on inner or outer table (and filters you apply on these tables in WHERE) are key here: While it seems the statement you quoted assumes always a kind of NESTED LOOP path between outer and inner tables (and moreover this seems to be "dictated" by indexes on the join fields) optimizers are much smarter than that and have lot more choices like Sort merge or hash joins (if they seem more efficient).

2. You prefer throughput over response time (wich means you want to have all rows in the fastest way OR you are interested to get the first rows as quick as possible). You can influence this by using the

hints (by OPEN SQL or native). I.e. for ORACLE's CBO throughput is the default goal wich will prefer

Sort merge or hash joins instead of NESTED LOOPS (wich , in turn, is best for response time)

3. You use rule optimization instead of cost based optimization: This is easy: because rule applies a NESTED LOOPS always when indexes are present (and ignores completyl data volume statsitics), otherwise the sort merge or hash is choosen

While you can find all three of them in databases supported by SAP it may that different databases handle the generating of execution plan different. Get your ST05 running and check it.

Some rules (out of others) for join optimization :

- for nested loops

the join index should contain also the fields used in the WHERE clause

while cost based optimizing takes care of join orders of tables (i.e. for a 4 table join you will have 24 possible join orders) if it has sufficient statsitical data, despite of it you can put the driving table with the most selective WHERE into the front. Thus filtering out data not needed at an early join stage

- for a sort merge/ hash join increase sort memory (database parameters ) and filter out rows not needed early

bye

yk

[former_member194613]

> do you have any proof or just feel so?

If he would have such a proof, then he would have not only 14 points ..

[Former Member]

Hi ,

Is this the way educated people respect each other.

feel bad this education and forum points has taught only these things to you all.

Thanks,

Shobana.K.

[valter_oliveira]

If he would have such a proof, then he would have not only 14 points ..

Although his contribution was not correct, this was not nice to say ...

Regards,

Valter Oliveira.

[former_member194613]

sorry, my response was only a small step further from Erics remark.

If somebody could prove which of the accesses join, nested select or FAE is always the best/optimal he would easily get a lot of points. He could contact me, if deserved I would convince the responsible persons ... but I think it will never happen

The good thing of my remark was the continuation of the discussion.

I think, there is no generally best solution for this question (as usually in performance), there are always execptions. I would be more inclined to state that joins are in general the best solution besides some exceptions:

One exception was mentioned above, the hierarchical relation between the 2 tables:

If that appears, then too much data must be transferred from database to application server for the join:

table A                  tableB
line_A1                  line_B11         
line_A1                  line_B12
                              ....
line_A1                  line_B1N
line_A2                  line_B21
...

line_A = selected columns of table A

line_B = selected columns of table B

The information coming from table A is transferred N times identically and this can be an overhead. But maybe N is small or line_B is anyway much larger than line_A, then the overhead is small. About Thomas question, I don't think that new databases change something in this behaviour. The search is faster than with any other access type.

Another exception appears when the optimizer comes to a wrong conclusion for the order in which the tables should be processed.

This appears when the ON-conditions have only fields which are not clearly realted to the existion indexes.

Assume the join has 3 tables, the fasted order would be A B and C

but the optimizer decides to use a different order.

Then a FAE with the order A, B and C can be faster.

BUT very often it is possible to influence the optimizer, for example with a hint, to use the order A B and C and then the join is again faster than the FAE.

No proof, no example, just my opinion. I would be interested to hear others.

Siegfried

[ThomasZloch]

I'm lacking some knowledge background when it comes to the actual database level, but your conclusion (freely translated) "joins are usually faster than other constructs" perfectly fits my practical Open SQL experience, as I also mentioned in other threads already. What I don't like is these generalistic "use FAE for better performance" statements we can read here so often, which are plain wrong imho, and mostly just copied and pasted from previous posts.

Thomas