Sql Server Join query

Question

I have two tables. one is a small table and another one is a large table. While joining between two table, which table i will keep in left and which one in right so that the query optimiser will search quicker or it does not matter where i will join the table..

for example :

--1
SELECT smalltable.column1,
       largetable.column1
  FROM smalltable
 INNER JOIN largetable
    ON smalltable.column1 = largetable.column1 ;
--2
SELECT smalltable.column1,
       largetable.column1
  FROM smalltable
 INNER JOIN largetable
    ON largetable.column1 = smalltable.column1 ;

Which query will make it faster or it doesnot matter.

It depends... What is your table structure & what is the output you need? — Yaqub Ahmad
– Yaqub Ahmad, Commented Nov 25, 2011 at 8:21

Stefan Mai · Accepted Answer · 2011-11-25 08:18:54Z

2

If you're talking about Microsoft SQL Server, both queries are equivalent to the query optimizer. In fact, to almost any cost-based query optimizer they'll be equivalent. You can try it by looking at the execucution plan (here for details http://www.simple-talk.com/sql/performance/execution-plan-basics/).

answered Nov 25, 2011 at 8:18

Stefan Mai

24.1k7 gold badges58 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

onedaywhen Over a year ago

Your answer directly contradicts the one by @lloydom. Care to comment?

Stefan Mai Over a year ago

It's more a philosophical debate, but I tend to say "lean into the optimizer as heavily as you can". The reason for this is twofold: first, any optimizations done in the future are "for free" performance increases for you. Secondly, in a cost-based optimizer, if the DBMS has to do the work to calculate statistics, the actual decision one way or another is not much "work", it already had to dredge up the data. Now you could easily make a counter argument for when you aren't maintaining adequate statistics but I think that should be addressed elsewhere. It's just a matter of preference.

TomTom · Accepted Answer · 2011-11-25 08:18:50Z

1

The query optimizer for most decent SQL Server variants will solve that. Some pritimitive ones dont (have a query optimizer - older MySQL, Access come to my mind). SOme may get overlaoded with complex decisions (this is simple).

But in general - trust the query optimizer first.

answered Nov 25, 2011 at 8:18

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Comments

Miika L. · Accepted Answer · 2011-11-25 08:20:20Z

1

It should not matter which order you use, as your SQL Server should optimise the query execution for you. However, (if you are using Microsoft SQL Server) you could use SQL Server Profiler (found under the Tools menu of SQL Server Management Studio) to check the execution plans of both options.

answered Nov 25, 2011 at 8:20

Miika L.

3,3491 gold badge27 silver badges36 bronze badges

Comments

lloydom · Accepted Answer · 2011-11-25 08:41:36Z

0

If one of the tables is smaller that the other table. Place the smaller table first and then the larger table as it will have less work to do and more over this will help the query optimizer to choose a plan that uses a Hash Join. Then run the query profiler and check that the Hash join is used because this is the best and fastest in this scenario. If there are no indexes on the joined tables then optimizer will select hash join. You can force a Hash join by using OPTION (HASH JOIN) after inner join statement

From MSDN,http://blogs.msdn.com/b/irenak/archive/2006/03/24/559855.aspx

The column name that joins the table is called a hash key. In the example above, it’ll be au_id. SQL Server examines the two tables being joined, chooses the smaller table (so called build input), and builds a hash table applying a hash algorithm to the values of a hash key. Each row is inserted into a hash bucket depending on the hash value computed for the hash key. If build input is done completely in-memory, the hash join is called an “in-memory hash join”. If SQL Server doesn’t have enough memory to hold the entire build input, the process will be done in chunks, and is called “grace hash join”.

answered Nov 25, 2011 at 8:41

lloydom

3872 silver badges11 bronze badges

2 Comments

onedaywhen Over a year ago

Your answer directly contradicts the one by @Stefan Mai. Care to comment?

lloydom Over a year ago

It is best to help the optimizer when you can, leaving everything to it is not a good idea because the computer resources are limited, for example if your are doing a 4 to 6 table join it would take longer for the optimizer to find the best plan itself and then run query so it then resorts to responding within a certain time and use the older plans.

Yaqub Ahmad · Accepted Answer · 2011-11-25 09:05:44Z

0

Before running both the queries,select'Include Actual Execution Plan' from the menu & then run the queries. The Sql server will show the execution plan which is the best tool to create the optimized queries. See more about Execution Plan here.

answered Nov 25, 2011 at 9:05

Yaqub Ahmad

27.7k24 gold badges105 silver badges151 bronze badges

Comments

Lynn Langit · Accepted Answer · 2012-05-11 23:44:28Z

0

The order of the join columns does matter. See this post for more detail. Also there has been no discussion of indexing in this thread. It is the combination of optimal join table order AND useful indexing that results in the fastest executing queries.

answered May 11, 2012 at 23:44

Lynn Langit

4,0601 gold badge25 silver badges31 bronze badges

Collectives™ on Stack Overflow

Sql Server Join query

6 Answers 6

2 Comments

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related