Does JOIN order effect efficiency?
-
Hi all,
Does JOIN order effect efficiency?
If I have three large tables to join together should I join the two that I know will cut the number of rows down a lot first and then join the 3rd table or does it make no difference (if I join the first and 3rd - which I know will be a large result set and then join the 2nd).
Thanks in advance,
Chiz.
-
It does make a difference. Check out the query plan set stats IO on and you can see for yourself.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Thanks.
I thought it would but just wanted to check. Makes sense.
-
Generally, the join order does not make much difference. SQL Server does a lot of work building the query plan and it normally ends up with the same plan regardless of the order of the joins. In fact, it may also end up with the same query plan for queries that look very different but are logically the same.
Checking the query plan is always a good thing. However, you do need to test with representative data (both volume and cardinality). Additionally, depending on the query, you may find that for some conditions (the same tables/joins but with different Where conditions) may produce a very different query plan.
-
The join order does not affect performance. The query optimiser will reorder the join to find the cheapest plan possible. I think it's been this way since SQL 7
I tried various orders of three tables, two large and one small and neither the exec plan not the io stats changed in any way.
There is, in fact, a query hint to over ride this behaviour (FORCE ORDER)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The join order can affect efficiency. It depends...
The SQL optimiser is a good cost-based optimiser, but it is not psychic - if you do not tell it something it could find useful then it will assume it has all the information that exists.
1) SQL cannot always do transitive predicate closure. SQL2000 and below can NEVER do this. This is the old algebra trick of 'If a = b and b = c then a = c' SQL 2005 can do this on equality predicates, but may not be able to do this for items like IN lists. Therefore you should always specify all your predicates.
For example, 'WHERE t1.c1 IN (1, 2, 5, 8) AND t1.c1 = t2.c2', SQL may not automatically know that t2.c2 is also IN (1, 2, 5, 8) - it may only know that t2.c2 = t1.c1 and could give a bad access path. If you specify 'WHERE t1.c1 IN (1, 2, 5, 8) AND t1.c1 = t2.c2 AND t2.c2 IN (1,2,5,8)' then SQL has all the facts to choose which table should be the primary table in a join, which could give you a better access path. This principle applies to all forms of complex predicates (E.G. NOT, Substring, LIKE, etc, etc)
2) If the optimiser works out that making t1 the primary table in a join costs the same as making t2 the primary table, it will use the first table mentioned in your statement as the primary table, which may not always be the best access path.
Therefore, as a good DBA you should know your data well enough to work out which table should be mentioned first in your statement.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply