http://stackoverflow.com/questions/2446927/sql-server-2008-join-hints
Can someone please tell me why applying LOOP hints to all my queries is a bad idea. I read somewhere that a LOOP JOIN is default JOIN method for query optimiser but couldn't verify the validity of the statement?
Because this robs the optimizer of the opportunity to consider other methods which can be more efficient.
When are JOIN hints used? When the hits the fan and ghost busters ain't in town?
When the data distribution (on which the optimizer makes its decisions) is severely skewed and the statistics are no able to represent it correctly.
What's the difference between LOOP, HASH and MERGE hints? BOL states that MERGE seems to be the slowest but what is the application of each hint?
These are different algorithms.
-
LOOP
is nested loops: for each record from the outer table, the inner table is searched for matches (using the index of available). Fastest when only a tiny portion of records from both tables satisfy theJOIN
and theWHERE
conditions. -
MERGE
sorts both tables are traverses them in the sort order, skipping the unmatched records. Fastest for theFULL JOIN
s and when both recordsets are already sorted (from previous sort operations or when the index access path is used) -
HASH
build a hash table in the temporary storage (memory ortempdb
) from one of the tables and searches it for each record from the other one. Fastest if the large portion of records from either table matches theWHERE
andJOIN
condition.