May 14, 2008 at 4:40 am
All,
I have some doubts on Join methods. it may be basic questions.I will explain it with a simple example.
Table A: 1000 Rows
Table B: 250 Rows
Key question is :
which table should come first when we are going to use JOIN ?
i.e A table which contains less number of rows than any other table used in the same join or A table which contains large number of rows than other table used in the same join.
My opinion is Large table should come first.But i could be wrong.
Here, I am going to use all kind of join with different combinations.
which one is fast ? Why ?
1) Table A Left Join Table B
2) Table B Left Join Table A
3) Table A Right Join Table B
4) Table B Right Join Table A
5) Table A Inner Join Table B
6) Table B Inner Join Table A
Inputs and suggestions are welcome ! It would be appreciable if anyone explain it with good example.
karthik
May 14, 2008 at 6:18 am
Q:Which table should come first when we are going to use JOIN ?
A: It does not matter with SQL Server as it will rewrite the query based on "costs". When a RDBMS (Relational Database Management Systems) uses a semantic optimizer instead of a cost optimizer, the table order is important, but the last major RDBMS that used a semantic optimizer was Oracle 7 and that was replaced in July of 1997 by Oracle 8, which has a cost-based optimizer.
Under SQL Server there is an option "FORCEPLAN": From Books OnLine:
When FORCEPLAN is set to ON, the Microsoft SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query. In addition, setting FORCEPLAN to ON forces the use of a nested loop join unless other types of joins are required to construct a plan for the query, or they are requested with join hints or query hints.
The last time I used FORCEPLAN was in 1999 under SQL Server 7. I do not use FORCEPLAN as the query optimizer is now smarter than me.
Q. My opinion is Large table should come first.But i could be wrong.
A: As indicated, the table order does not matter.
Q: am going to use all kind of join with different combinations.
A: Some of the joins are equal:
1) "Table A Left Join Table B" = "Table B Right Join Table A"
2) "Table B Left Join Table A" = "Table A Right Join Table B"
3) "Table A Inner Join Table B" = "Table B Inner Join Table A"
But, there these are categories are different and produce different results, that is
"Table A Left Join Table B" is not the same as "Table B Left Join Table "A"
Q: which one is fast ?
A: As "Fast" is a relative term, this question is not easily answerable.
SQL = Scarcely Qualifies as a Language
May 14, 2008 at 6:58 am
Ran some simple tests (A=25K records B=780K records) and found no difference in performance in the two variants of the sql syntax that use INNER JOIN.
May 19, 2008 at 10:20 am
[font="Verdana"]Cross Post
http://www.sqlservercentral.com/Forums/Topic500333-8-1.aspx
Mahesh[/font]
MH-09-AM-8694
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy