SQL Server 2008中的循环联接 [英] Loop Join in SQL Server 2008

问题描述

我不清楚下面提到的查询之间的工作差异.

I'm not clear about working difference between queries mentioned below.

具体来说，我不清楚OPTION(LOOP JOIN)的概念.

第一种方法::它是一种传统的联接，使用起来比下面的所有联接都要昂贵.

1st approach: it's a traditional join used, which is most expensive than all of below.

SELECT * 
FROM [Item Detail] a
LEFT JOIN [Order Detail] b ON a.[ItemId] = b.[fkItemId] OPTION (FORCE ORDER);

第二种方法:它在已排序数据的语句中包含OPTION，只是经过优化.

2nd approach: It includes OPTION in a statement with sorted data, merely optimized.

SELECT * 
FROM [Item Detail] a
LEFT LOOP JOIN [Order Detail] b ON a.[ItemId] = b.[fkItemId] OPTION (FORCE ORDER);

第三种方法:在这里，我不清楚查询的工作方式，并且将OPTION与loop join一起包含！！?

3rd approach: Here, I am not clear, how the query works and includes OPTION with loop join!!?

SELECT * 
FROM [Item Detail] a
LEFT LOOP JOIN [Order Detail] b ON a.[ItemId] = b.[fkItemId] OPTION (LOOP JOIN);

有人能解释彼此之间的区别和工作方式以及优势吗?

Can anybody explain difference and way of working and advantages of each one over other?

注意:这些是不是嵌套或哈希循环！

Note: These are not Nested OR Hash loops!

推荐答案

来自查询提示(Transact-SQL)

FORCE ORDER指定查询所指示的连接顺序 语法在查询优化过程中保留.使用FORCE ORDER可以 不会影响查询优化器的角色反转行为.

FORCE ORDER Specifies that the join order indicated by the query syntax is preserved during query optimization. Using FORCE ORDER does not affect possible role reversal behavior of the query optimizer.

也

{循环|合并| HASH} JOIN指定所有联接操作都是 在整个查询中由LOOP JOIN，MERGE JOIN或HASH JOIN执行. 如果指定了多个连接提示，则优化器将选择 允许的策略中最便宜的加入策略.

{ LOOP | MERGE | HASH } JOIN Specifies that all join operations are performed by LOOP JOIN, MERGE JOIN, or HASH JOIN in the whole query. If more than one join hint is specified, the optimizer selects the least expensive join strategy from the allowed ones.

高级查询调整概念

如果一个联接输入很小(少于10行)，而另一个联接 输入是相当大的，并且在其连接列上建立了索引，嵌套了一个索引 循环连接是最快的连接操作，因为它们需要 最少的I/O和最少的比较.

If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed on its join columns, an index nested loops join is the fastest join operation because they require the least I/O and the fewest comparisons.

如果两个联接输入不小但按联接排序 列(例如，如果它们是通过扫描排序获得的 索引)，则合并联接是最快的联接操作.

If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation.

哈希联接可以有效地处理大型的，未排序的，未索引的输入.

Hash joins can efficiently process large, unsorted, nonindexed inputs.

加入提示(Transact-SQL)

联接提示指定查询优化器强制执行联接策略 在两个表之间

Join hints specify that the query optimizer enforce a join strategy between two tables

您的选项1告诉优化器保持连接顺序不变.因此，JOIN类型可以由优化程序确定，所以可能是MERGE JOIN.

Your option 1 tells the optimizer to keep the join order as is. So the JOIN type can be decided by the optimizer, so might be MERGE JOIN.

您的选项2告诉优化器对此特定的JOIN使用LOOP JOIN.如果FROM部分中还有任何其他联接，则优化程序将能够为它们做出决定.另外，您要指定要为优化程序采用的JOINS顺序.

You option 2 is telling the optimizer to use LOOP JOIN for this specific JOIN. If there were any other joins in the FROM section, the optimizer would be able to decide for them. Also, you are specifying the order of JOINS to take for the optimizer.

您的最后一个选项OPTION (LOOP JOIN)将对查询中的所有联接强制执行LOOP JOIN.

Your last option OPTION (LOOP JOIN) would enforce LOOP JOIN across all joins in the query.

总而言之，优化器很少会选择错误的计划，这可能表明存在更大的潜在问题，例如过时的统计信息或索引碎片.

这篇关于SQL Server 2008中的循环联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！