May 18, 2013 at 10:46 pm
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
May 19, 2013 at 3:16 am
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
May 19, 2013 at 9:36 am
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?
May 19, 2013 at 9:46 am
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
May 19, 2013 at 9:54 am
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
May 19, 2013 at 2:48 pm
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?
May 19, 2013 at 3:54 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply