Specifically I’m unclear about the concept of
OPTION(LOOP JOIN).
第一种方法：这是一种使用的传统连接,这比以下所有都是最贵的.
SELECT * FROM [Item Detail] a LEFT JOIN [Order Detail] b ON a.[ItemId] = b.[fkItemId] OPTION (FORCE ORDER);
第二种方法：它包括OPTION在带有排序数据的语句中,只进行了优化.
SELECT * FROM [Item Detail] a LEFT LOOP JOIN [Order Detail] b ON a.[ItemId] = b.[fkItemId] OPTION (FORCE ORDER);
第三种方法：在这里,我不清楚,查询是如何工作的,包括OPTION循环连接!!
SELECT * FROM [Item Detail] a LEFT LOOP JOIN [Order Detail] b ON a.[ItemId] = b.[fkItemId] OPTION (LOOP JOIN);
任何人可以解释每个人的差异和工作方式以及优势吗？
注意：这些不是嵌套或哈希循环！
解决方法
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.
也
{ 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.
Advanced Query Tuning Concepts
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.
Join hints specify that the query optimizer enforce a join strategy
between two tables
您的选项1告诉优化程序保持连接顺序.所以JOIN类型可以由优化器决定,所以可能是MERGE JOIN.
您的选项2告诉优化器为此特定JOIN使用LOOP JOIN.如果FROM部分中有其他连接,则优化器将能够为它们决定.此外,您指定优化器采用的JOINS顺序.
您的最后一个选项OPTION(LOOP JOIN)将在查询中的所有连接中执行LOOP JOIN.
这一切都说,优化器很少选择不正确的计划,这应该可能表明更大的基础问题,如过时的统计数据或分段索引.
