December 26, 2017 at 9:15 pm
Hi All,
Does the order in which the tables are placed inside a join matter for getting better performance?
Assuming, below query what is the best way to re-arrange the join order? what considerations to be taken care?
select
t1.c1,t1.c2,t1.c3,t1.c6,
t2.c1,t2.c3,t2.c4,
t3.c1,t3.c2,t3.c3,
t4.c1,t4.c2,t4.c3
from t1 inner join t2 on t1.c2 = t2.c4
inner join t3 on t3.c1 = t2.c3 and t3.c3 = t1.c6
inner join t4 on t4.c2 = t3.c1
where t1.iscurrent = 1
and t3.dt between 'date-1' and 'date-2'
sample rowcounts
================
t1 = 150 rows
t2 = 9634765 rows
t3 = 9000 rows
t4 = 70 rows
Thanks,
Sam
December 27, 2017 at 7:40 am
vsamantha35 - Tuesday, December 26, 2017 9:15 PMHi All,Does the order in which the tables are placed inside a join matter for getting better performance?
Assuming, below query what is the best way to re-arrange the join order? what considerations to be taken care?
select
t1.c1,t1.c2,t1.c3,t1.c6,
t2.c1,t2.c3,t2.c4,
t3.c1,t3.c2,t3.c3,
t4.c1,t4.c2,t4.c3
from t1 inner join t2 on t1.c2 = t2.c4
inner join t3 on t3.c1 = t2.c3 and t3.c3 = t1.c6
inner join t4 on t4.c2 = t3.c1
where t1.iscurrent = 1
and t3.dt between 'date-1' and 'date-2'sample rowcounts
================
t1 = 150 rows
t2 = 9634765 rows
t3 = 9000 rows
t4 = 70 rowsThanks,
Sam
Join order won't define execution order most of the times. My suggestion is that you write your JOINs in a logical order that would make easier for developers to read and modify.
December 27, 2017 at 7:55 am
Luis Cazares - Wednesday, December 27, 2017 7:40 AMvsamantha35 - Tuesday, December 26, 2017 9:15 PMHi All,Does the order in which the tables are placed inside a join matter for getting better performance?
Assuming, below query what is the best way to re-arrange the join order? what considerations to be taken care?
select
t1.c1,t1.c2,t1.c3,t1.c6,
t2.c1,t2.c3,t2.c4,
t3.c1,t3.c2,t3.c3,
t4.c1,t4.c2,t4.c3
from t1 inner join t2 on t1.c2 = t2.c4
inner join t3 on t3.c1 = t2.c3 and t3.c3 = t1.c6
inner join t4 on t4.c2 = t3.c1
where t1.iscurrent = 1
and t3.dt between 'date-1' and 'date-2'sample rowcounts
================
t1 = 150 rows
t2 = 9634765 rows
t3 = 9000 rows
t4 = 70 rowsThanks,
Sam
Join order won't define execution order most of the times. My suggestion is that you write your JOINs in a logical order that would make easier for developers to read and modify.
I would clarify that this is true only when all of the joins are INNER or CROSS joins. Once you add OUTER joins, the order definitely does make a difference.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 27, 2017 at 8:11 am
drew.allen - Wednesday, December 27, 2017 7:55 AMLuis Cazares - Wednesday, December 27, 2017 7:40 AMJoin order won't define execution order most of the times. My suggestion is that you write your JOINs in a logical order that would make easier for developers to read and modify.
I would clarify that this is true only when all of the joins are INNER or CROSS joins. Once you add OUTER joins, the order definitely does make a difference.
Drew
And my advice would still be to write it on the most logical way possible.
December 27, 2017 at 9:02 pm
Not sure where I read it and couldn't recollect, but I read that in the JOIN the table on the right should have less number of rows then the table on left, if so, it will improve the performance. How far is it true????
December 28, 2017 at 1:29 am
vsamantha35 - Wednesday, December 27, 2017 9:02 PMNot sure where I read it and couldn't recollect, but I read that in the JOIN the table on the right should have less number of rows then the table on left, if so, it will improve the performance. How far is it true????
I'm not sure how true that is... what about a self-join?
Surely the speed of the joins is dependent on the columns being joined (i.e. key columns vs non key columns), whether those columns are indexed, up-to-date statistics, and a tiny little bit of data type (e.g. int or varchar).
I wouldn't say row count is an indicator for performance
December 28, 2017 at 2:18 am
vsamantha35 - Wednesday, December 27, 2017 9:02 PMNot sure where I read it and couldn't recollect, but I read that in the JOIN the table on the right should have less number of rows then the table on left, if so, it will improve the performance. How far is it true????
This might be true when the data is only able to be accessed in heaps (where there are no indexes, or no usable indexes, as well as no Primary and Foreign Keys), however, in a well designed database this is unlikely to be the case.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 28, 2017 at 4:09 am
vsamantha35 - Wednesday, December 27, 2017 9:02 PMNot sure where I read it and couldn't recollect, but I read that in the JOIN the table on the right should have less number of rows then the table on left, if so, it will improve the performance. How far is it true????
Might have been true back in SQL Server 7 or earlier.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 28, 2017 at 11:44 am
Thanks for the clarifications.
December 28, 2017 at 12:02 pm
Or if you are using a query hint to force the order.
December 28, 2017 at 1:49 pm
not using query hints at this point.
December 28, 2017 at 1:55 pm
vsamantha35 - Thursday, December 28, 2017 1:49 PMnot using query hints at this point.
Good, don't, unless absolutely needed. I haven't used any in the code I write.
December 28, 2017 at 3:33 pm
Order of tables in the from clause should not matter . The optimizer will figure out the best way to do it in theory and the optimizer is smarter than you are. However, if you're working with old Oracle programmers, the first releases of Oracle actually went to the from clause and loaded the tables into the parser in reverse order off of the push down stack. Back then, it really made a big difference. The other thing that can make a difference is if you use parentheses and the in fixed join operation . I hope you know that outer joins do not commute, unlike inner joins. But officially in the ANSI/ISO standard SQL, the parentheses are supposed to define the "effectively executed as" order of execution. We defined the SQL statements is being executed in left to right order with the usual operator precedence of the parens. We could not specify, in an ANSI ISO standard, all of the rules for rearranging a query, so we didn't even try. Instead, we started with the word "effectively" and said it was up to the implementation. In fact, the you're not aware of it, there's a lot of things in standard SQL that are done "effectively" and we mean that you get the same results as if they had been done in a deterministic pattern.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply