SQL SERVER 2008 JOIN Tips

Question

Recently, I tried to optimize this query

UPDATE Analytics
SET UserID = x.UserID
FROM Analytics z 
INNER JOIN UserDetail x ON x.UserGUID = z.UserGUID

The estimated execution plan shows 57% in updating the table and 40% in hash match (aggregate). I felt a little and came across the topic of JOIN hints. So I added a LOOP hint to my inner join and WA-ZHAM! The new execution plan shows 38% in the table update and 58% in the index search.

So I was about to start applying LOOP hints to all of my requests, until prudence improved me. After some googling, I realized that JOIN hints are not very well covered in BOL . Therefore...

• Can someone tell me why applying LOOP hints to all my queries is a bad idea. I read somewhere that LOOP JOIN is the default JOIN method for query optimizer, but cannot verify the validity of the statement?
• When are JOIN hints used? When does sh * t get into the fan and the ghost intruders are not in the city?
• What is the difference between LOOP, HASH and MERGE hints? BOL claims that MERGE seems the slowest, but what is the use of each hint?

Thanks for your time and help people!

I am running SQL Server 2008 BTW. The statistics mentioned above are planned implementation plans.

Answer 1

-, , , LOOP- - . - , LOOP JOIN JOIN , ?

, .

JOIN? sh * t , ?

( ) , .

LOOP, HASH MERGE? BOL , MERGE , ?

.

• LOOP - : ( ). , JOIN WHERE.

• MERGE , . FULL JOIN ( )

• HASH - ( tempdb) . , WHERE JOIN.

Answer 2

57% 40% (). . LOOP WA-ZHAM! 38% 58% .

, ? , , .