views:

80

answers:

3

Which Join having more I/O and CPU Cycle ? Hash join or Merge join or Loop join?

+1  A: 

Check out the execution plan for your query to find out

Matthew Whited
Generaly which has as per your experience?
Paresh
Actully i was tried to see, but can't figure out.means want to figure our which haveing more cpu cycle ans which has I/O.
Paresh
You're right that checking an execution plan will tell you which join is being performed. But if you don't know how to cause each type to be used by the engine, it would be difficult to test... Might be better to refer to Books Online (since the question is tagged as tsql / sql-server)
Neil Fenwick
I looked at a few links and was going to post them but I don't have time to review them and make sure they are good right now. Typically the more work the join would have to do the worse it will perform. so hash queries (varchars and so on) will perform worse than indexed and binary queries (numeric fields) but it really depends on what you are trying to do in the join
Matthew Whited
A: 

If I remember correctly, it would be (most expensive to least):

Loop Join
Merge Join
Hash Join

Justin Niessner
+4  A: 

Short answer: Loop join

In order of least efficient to most efficient

  1. The least efficient Loop join checks each value of the "left" table against each value of the "right" table. (Like a nested for-loop in programming)
  2. The most commonly occurring join is a hash join where the smallest table has hash values pre-calculated for the keys being tested. The hash is then tested against the other table. Usually this is used when each table is not in the same sort order before the process beings.
  3. Most efficient of all is the merge join. This is used when both tables are originally stored on disk in the same order (ie have clustered indexes and both in the same order). Here the algorithm steps through both tables at the same time and skips over sections where they don't overlap.
Neil Fenwick
Actually if it helps and you're interested in going into this in more depth - I think I originally learned this from "Books Online" (installed with Sql Server client tools)
Neil Fenwick