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
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8809 Visits: 1264
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 (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)

Group: General Forum Members
Points: 160655 Visits: 22697
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 (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62293 Visits: 16619
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 (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)

Group: General Forum Members
Points: 160655 Visits: 22697
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
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8809 Visits: 1264
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
Say Hey Kid
Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)Say Hey Kid (703 reputation)

Group: General Forum Members
Points: 703 Visits: 754
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 (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80131 Visits: 20347
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 (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)SSC Guru (895K reputation)

Group: General Forum Members
Points: 895992 Visits: 48655
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
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8809 Visits: 1264
Thanks for the clarifications.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)

Group: General Forum Members
Points: 385753 Visits: 42541
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