topic Re: Inner Join and performance in Application Development and Automation Discussions

Inner Join and performance

Former Member — Fri, 14 Aug 2009 12:28:53 GMT

Hello experts,

i have similar SQL, which is running long time

SELECT x_{kunnr x}matnr x_{vtweg x}datbi

z~knumh

APPENDING CORRESPONDING FIELDS OF TABLE t_acust

FROM yhdwt_004 AS x INNER JOIN knvv AS y

ON x_{kunnr = y}kunnr

AND x_{vkorg = y}vkorg

AND x_{vtweg = y}vtweg

INNER JOIN a950 AS z

ON y_{vkbur = z}vkbur

WHERE z~kappl = 'V '

AND z~kschl IN ('Z950', 'Z954')

AND z~datbi IN lr_datbi

AND z~datab IN lr_datab.

and my question - > Can we say that order of tables in select is important for performance (maybe optimalizator do select for first and second tables and after that third )?

FROM yhdwt_004 AS x INNER JOIN knvv AS y INNER JOIN a950 AS z

FROM knvv AS x INNER JOIN yhdwt_004 AS y INNER JOIN a950 AS z

....

yhdwt_004 - several million rows

knvv - several hundred thousend rows

a950 - thousend rows

Thanks for feedback

Martin

Re: Inner Join and performance

RaymondGiuseppi — Fri, 14 Aug 2009 12:45:26 GMT

Use [ST05|https://forums.sdn.sap.com/search.jspa?objID=f234&q=ST05] and run your report in different versions to get some information to compare.

Also and perform some search with keywords [join order table select |https://wiki.sdn.sap.com/wiki/dosearchsite.action?searchQuery.queryString=joinordertable+select&searchQuery.spaceKey=conf_global]

Regards,

Raymond

Re: Inner Join and performance

HermannGahm — Fri, 14 Aug 2009 12:46:51 GMT

Hi Martin,

the order of tables in joins is not important in the coding: Normally the opitmizer determines the join order based on statistics. The order of the tables in the coding is not important.

However when it comes to the execution on the database the order of the table is very important. But the opitmizer should be able to figure out the best order. If not you might want to help the optimizer and only then the order of the tables might be important as well.

So first start with reading the execution plan.

What order does the optimzer choose? And what join type does the optimzer choose? (Nested Loops, Sort Merge or Hash?)

For Nested Loops the optimizer should start with the smallest RESULTSET (not necessarily the smalles table). Map your WHERE conditions to the tables to see which table has the smalles result set. For nested loops make sure the optimizer starts here.

More things to check: Do you have index support for where and Join donditions? Is any table a buffered table (A950?) if so, try to youse the buffer and remove the table from the join.

Kind regards,

Hermann

Re: Inner Join and performance

Former Member — Fri, 14 Aug 2009 12:56:10 GMT

What does it mean "buffered table" ?

Please some example for - > to use the buffer and remove the table from the join.

Thanks

Re: Inner Join and performance

HermannGahm — Fri, 14 Aug 2009 13:06:14 GMT

Hi Martin,

you can check the buffer settings in transaction SE13.

But again, more important is that your nested loop joins start with the

smallest result set. So work that out first.

Generally your where and join conditions should be supported with indexes (for often executed sql statements)

and generally you should not access buffered tables in joins (since the buffer can not be used).

In such a case do the join select on the other tables and then access the buffered table e.g. in a loop

over the result set from the join.

Kind regards,

Hermann

Re: Inner Join and performance

Former Member — Fri, 14 Aug 2009 13:25:24 GMT

in principle the order should not have an influence, but I am not 100% sure that it can not.

Still I would always recommend you the following order, a is the smallest table and only one with conditions in WHERE.


FROM          a950 AS a
INNER JOIN  knvv AS b
ON b~vkbur = a~vkbur
INNER JOIN   yhdwt_004 AS c 
ON    c~kunnr = b~kunnr
AND c~vkorg  = b~vkorg
AND c~vtweg = b~vtweg
WHERE a~kschl IN ('Z950', 'Z954')
AND     a~datbi IN lr_datbi
AND     a~datab IN lr_datab
AND     a~kappl = 'V '

Re: Inner Join and performance

Former Member — Fri, 14 Aug 2009 14:05:30 GMT

Hi Martin,

Probable Cause of Issue

You say that your custom table YHDWT_004 has several million records yet you do not seem to be restricting records on this table. Based on the way you have constructed your query, I am led to believe that you are iterating through all the records in the huge custom table.

Recommended Solution

1) Your where clause seems to be restricting only table A950. It makes sense starting with this table first so as to limit your iterations. Construct your query in the following dircetion.

A950 -> KNVV -> YHDWT_004.

2) Evaluate the possibility of creating an index on database table YHDWT_004 on the fields KUNNR VKORG and VTWEG (if such an index does not already exist).