CODEWITHSUNDEEP
sql
akd das
akd das

Reputation: 77

Sql Server Join query

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.

Upvotes: 1

Views: 1592

Answers (6)

Lynn Langit
Lynn Langit

Reputation: 4060

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.

Upvotes: 0

Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

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.

Upvotes: 0

lloydom
lloydom

Reputation: 387

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”.

Upvotes: 0

Miika L.
Miika L.

Reputation: 3353

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.

Upvotes: 1

Stefan Mai
Stefan Mai

Reputation: 23939

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/).

Upvotes: 2

TomTom
TomTom

Reputation: 62093

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.

Upvotes: 1

Related Questions