Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Order of joins - Which one is better? Expand / Collapse
Author
Message
Posted Saturday, May 18, 2013 10:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 19, 2014 8:33 PM
Points: 1,285, Visits: 2,963
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




Post #1454313
Posted Sunday, May 19, 2013 3:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 40,183, Visits: 36,589
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 2008, MVP
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

Post #1454319
Posted Sunday, May 19, 2013 9:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 19, 2014 8:33 PM
Points: 1,285, Visits: 2,963
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?
Post #1454342
Posted Sunday, May 19, 2013 9:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 1,917, Visits: 19,593
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
Post #1454343
Posted Sunday, May 19, 2013 9:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 40,183, Visits: 36,589
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 2008, MVP
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

Post #1454344
Posted Sunday, May 19, 2013 2:48 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 19, 2014 8:33 PM
Points: 1,285, Visits: 2,963
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?
Post #1454371
Posted Sunday, May 19, 2013 3:54 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:10 AM
Points: 40,183, Visits: 36,589
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 2008, MVP
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

Post #1454375
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse