| Author |
Topic |
|
ianmain
Starting Member
7 Posts |
 Posted - 2007-02-05 : 17:11:05
|
| 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.Chiz |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-02-05 : 20:18:34
|
| It shouldn't matter. The optimiser will decide the best course of action (mostly...)DavidMProduction is just another testing cycle |
 |
|
|
ianmain
Starting Member
7 Posts |
Posted - 2007-02-05 : 20:39:29
|
| Thanks mate.LOL: Production is just another testing cycle.So true!(I presume you work for Microsoft)Chiz |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-02-05 : 21:25:49
|
| >>(I presume you work for Microsoft)Not a chance mate... they work too hard :-)DavidMProduction is just another testing cycle |
|
|
|
ianmain
Starting Member
7 Posts |
Posted - 2007-02-05 : 21:52:44
|
| Must admit, I posted this question in another forum and got the answer: the order definitely does make a difference.Chiz |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-02-05 : 22:12:30
|
| >>Must admit, I posted this question in another forum and got the answer: the order definitely does make a difference.That news to me... Only on complex quieries do I see the optomiser needing a hand to make the best choice.DavidMProduction is just another testing cycle |
|
|
|
ianmain
Starting Member
7 Posts |
Posted - 2007-02-05 : 22:28:54
|
| I'm not a SQL Guru (laugh) - ASP.NET is my thing...But I'd have thought that the optimiser can only optimise the algorithm "logic" (if that makes sense) but can't take into account the size of data (becuse that's dynamic) in each table its about to join....but I'm really just guessing. :)Maybe the reply on the other forum is wrong.Chiz |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-05 : 22:32:12
|
Can you kindly provide us a link of this topic in the other forum ?
KH |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-02-05 : 22:37:06
|
| >>but can't take into account the size of data (becuse that's dynamic) in each table its about to join.That is incorrect. SQL Server keeps vast amounts of statistical data (histogram) to help do exactly this. One of the major benefits of modern SQL DBMS's is that you tell it WHAT data you need and the DBMS figures out the HOW.DavidMProduction is just another testing cycle |
|
|
|
ianmain
Starting Member
7 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-05 : 23:20:16
|
| Before you start comparing query plans:Make sure you have representative data loaded (i.e. both volume and type of data)Make sure the indexes are defragged or rebuiltMake sure that your Statistics up to dateKristen |
|
|
|
|
Does JOIN order effect efficiency?