SQL Server - Joins - Performance

Asked By Shiv Yorker on 02-Oct-11 09:09 AM
Hi all!

           I have two tables called tabA and tabB with 120000 and 15000 rows respectively. If i want to join these two tables then, which one should be on left hand side for better performance.  Which is better in the following?

     select a.Col1,b.Col2 from tabA a join tabB b on a.Id=b.Id

                     (or)
     
      select a.Col1,b.Col2 from tabB b join tabA a on b.Id=a.Id



Thanks,
Shiva
Peter Bromberg replied to Shiv Yorker on 02-Oct-11 09:41 AM
I don't think it really matters provided that you have good indexes.
dipa ahuja replied to Shiv Yorker on 02-Oct-11 09:52 AM
Untitled document
Focus on increasing the performance of the select query. Using which you can increase the speed of your query. As you have large no. of records

Tips For Optimizing Sql Server 2005 Query Performance
 
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old data
8. Partition your data correctly
9. Remove user-defined inline scalar functions
10. Use APPLY
11. Use computed columns
12. Use the correct transaction isolation level
 
Anoop S replied to Shiv Yorker on 02-Oct-11 10:25 AM
I think (not sure)  there will be performance issue when you join, generally while join use foreign key table left side and primary key table right side, because if you put foreign key table left side and primary key table right side the relation occur is many to one, and if put reverse the relation occur is one to many, so nee to search in all row.
easy way try it executing both query in sql server, in execution plan you can see time taken by the two query and you can find which one is better
Shiv Yorker replied to Anoop S on 02-Oct-11 12:39 PM
thanks anoop :-)