SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


suggestion on table order inside a join ?


suggestion on table order inside a join ?

Author
Message
vsamantha35
vsamantha35
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7445 Visits: 1164
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
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146934 Visits: 22080
vsamantha35 - Tuesday, December 26, 2017 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


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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
drew.allen
drew.allen
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56237 Visits: 14772
Luis Cazares - Wednesday, December 27, 2017 7:40 AM
vsamantha35 - Tuesday, December 26, 2017 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


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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

Group: General Forum Members
Points: 146934 Visits: 22080
drew.allen - Wednesday, December 27, 2017 7:55 AM
Luis Cazares - Wednesday, December 27, 2017 7:40 AM


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


And my advice would still be to write it on the most logical way possible.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
vsamantha35
vsamantha35
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7445 Visits: 1164
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????
DimUser
DimUser
SSChasing Mays
SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)

Group: General Forum Members
Points: 619 Visits: 706
vsamantha35 - Wednesday, December 27, 2017 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????


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
Thom A
Thom A
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70135 Visits: 18439
vsamantha35 - Wednesday, December 27, 2017 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????


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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
GilaMonster
GilaMonster
SSC Guru
SSC Guru (834K reputation)SSC Guru (834K reputation)SSC Guru (834K reputation)SSC Guru (834K reputation)SSC Guru (834K reputation)SSC Guru (834K reputation)SSC Guru (834K reputation)SSC Guru (834K reputation)

Group: General Forum Members
Points: 834488 Visits: 48500
vsamantha35 - Wednesday, December 27, 2017 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????


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


vsamantha35
vsamantha35
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7445 Visits: 1164
Thanks for the clarifications.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (352K reputation)SSC Guru (352K reputation)SSC Guru (352K reputation)SSC Guru (352K reputation)SSC Guru (352K reputation)SSC Guru (352K reputation)SSC Guru (352K reputation)SSC Guru (352K reputation)

Group: General Forum Members
Points: 352369 Visits: 41673
Or if you are using a query hint to force the order.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search