SQL SERVER 2008 JOIN提示 [英] SQL SERVER 2008 JOIN hints

问题描述

最近，我正在尝试优化此查询

Recently, I was trying to optimise this query

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

估计的执行计划在表更新中显示57％，在哈希匹配(汇总)中显示40％.我窥探了一下，并遇到了JOIN提示的主题.因此，我在内部联接和WA-ZHAM中添加了一个LOOP提示！新的执行计划在表更新中显示38％，在索引查找中显示58％.

Estimated execution plan show 57% on the Table Update and 40% on a Hash Match (Aggregate). I did some snooping around 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% on the Table Update and 58% on an Index Seek.

因此，我将开始对所有查询应用LOOP提示，直到谨慎使我变得更好.经过一番谷歌搜索后，我意识到 BOL 不能很好地覆盖JOIN提示.因此...

So I was about to start applying LOOP hints to all my queries until prudence got the better of me. After some googling, I realised that JOIN hints are not very well covered in BOL. Therefore...

1. 有人可以告诉我为什么对我所有的查询应用LOOP提示是一个坏主意.我在某处读到，LOOP JOIN是查询优化程序的默认JOIN方法，但无法验证该语句的有效性?
2. 何时使用JOIN提示?当被敌人击中时，风扇和幽灵破坏者不在城里吗?
3. LOOP，HASH和MERGE提示之间有什么区别? BOL指出，MERGE似乎是最慢的，但是每个提示的用途是什么?

感谢您的时间并为人们提供帮助！

Thanks for your time and help people!

我正在运行SQL Server 2008 BTW.上面提到的统计数据是估计的执行计划.

I'm running SQL Server 2008 BTW. The statistics mentioned above are ESTIMATED execution plans.

推荐答案

有人可以告诉我为什么对我所有的查询应用LOOP提示是一个坏主意.我在某处读到，LOOP JOIN是查询优化程序的默认JOIN方法，但无法验证该语句的有效性?

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.

何时使用JOIN提示?当被敌人击中时，迷幻破坏者不在城里吗?

When are JOIN hints used? When the sh*t 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.

LOOP，HASH和MERGE提示之间有什么区别? BOL指出MERGE似乎是最慢的，但是每个提示的用途是什么?

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?

这些是不同的算法.

1. LOOP是嵌套循环:对于外部表中的每个记录，将在内部表中搜索匹配项(使用可用索引).当两个表中只有一小部分记录满足JOIN和WHERE条件时最快.

1. 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 the JOIN and the WHERE conditions.

MERGE对两个表进行排序将以排序顺序遍历它们，从而跳过不匹配的记录. FULL JOIN和两个记录集都已排序(从以前的排序操作或使用索引访问路径开始)时最快

MERGE sorts both tables are traverses them in the sort order, skipping the unmatched records. Fastest for the FULL JOINs and when both recordsets are already sorted (from previous sort operations or when the index access path is used)

HASH从其中一个表在临时存储(内存或tempdb)中构建一个哈希表，并从另一个表中搜索每个记录.如果任一表中的大部分记录都符合WHERE和JOIN条件，则最快.

HASH build a hash table in the temporary storage (memory or tempdb) 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 the WHERE and JOIN condition.

这篇关于SQL SERVER 2008 JOIN提示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！