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


Order of joins - Which one is better?


Order of joins - Which one is better?

Author
Message
curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7716 Visits: 3696
Seems like order of joins completely changes the execution plan. In my first query ,i am joining on a column at the end from first table, in my second query i am joining on a column first. Which one is preferred? Left join or Inner joins?



SELECT R.PatientId,
P.Name
FROM Reg R
INNER JOIN PatientInfo P
ON R.Id = P.ID
LEFT JOIN AccountDetails A
ON P.AccountNumber = A.AccountNumber
LEFT JOIN ChargeAudit C
ON R.id = C.id
---------------------------------------------------

SELECT R.PatientId,
P.Name
FROM Reg R
LEFT JOIN ChargeAudit C
ON R.id = C.id
INNER JOIN PatientInfo P
ON R.Id = P.ID
LEFT JOIN AccountDetails A
ON P.AccountNumber = A.AccountNumber





GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232507 Visits: 46360
Completely irrelevant. Order that you specify the joins is not the order they are executed in, unless you're forcing join order.

Check that the queries are logically equivalent.

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


curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7716 Visits: 3696
GilaMonster (5/19/2013)
Completely irrelevant. Order that you specify the joins is not the order they are executed in, unless you're forcing join order.

Check that the queries are logically equivalent.


In my actual query i have changed the order just like the one in example and my execution plan and execution timings are much better?
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12562 Visits: 37673
care to share your table scripts / indexes / sample data scripts that demonstrates your issue?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232507 Visits: 46360
If you have different plans then either:

* The queries are not logically equivalent
* The different form resulted in the optimiser searching a different area of the plan space and finding a different optimal plan. Since that's dependent on the optimiser's estimates, heuristics and search algorithms this will not be consistent.

As for 'different performance', you ran multiple tests, ignored runs that incurred the overhead of compiling and data caching, analysed the results and came up with a statistically significant change?

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


curious_sqldba
curious_sqldba
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7716 Visits: 3696
GilaMonster (5/19/2013)
If you have different plans then either:

* The queries are not logically equivalent
* The different form resulted in the optimiser searching a different area of the plan space and finding a different optimal plan. Since that's dependent on the optimiser's estimates, heuristics and search algorithms this will not be consistent.

As for 'different performance', you ran multiple tests, ignored runs that incurred the overhead of compiling and data caching, analysed the results and came up with a statistically significant change?


My thinking is , i should have all the corresponding joins together then move on to other join with another table?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232507 Visits: 46360
Doesn't matter.
SQL is a declarative language, you tell SQL what you want and it figures out how to get that. Part of that figuring out is picking a good join order based on the data distribution and volume, not based on the order you specify them in the FROM clause.

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


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