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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply