为什么在嵌套循环(内部联接)之前进行排序操作? [英] Why is Sort operation before Nested Loops (Inner Join)?

问题描述

我有 2 个 Sql Server 实例和一个查询:

I have 2 Sql Server instances, and one query:

SELECT 
    [DetailDescription],
    [SubTotal]
FROM [dbo].[CRR] WITH (INDEX (IX_CORM_CORMId))
WHERE CORM_CORMId >= 5933168 AND CORM_CORMId <= 5955843

这导致了 2 个查询执行计划:

This leads to 2 query execution plans:

这里是上传的版本

一个有 Sort 的得到 301740 行，需要 48s，另一个得到 286743 行，没有 Sort 需要 5s.一个数据库是其他数据库的过时副本.表中行数的顺序为 98 419 368.

One with Sort gets 301740 rows and takes 48s, other gets 286743 rows without Sort takes 5s. One db is a bit outdated copy of other. Order of rows number in table is 98 419 368.

我的问题是:

1. 我不明白为什么嵌套循环连接需要排序?我试图用选项(QUERYTRACEON 2340)"禁用它.该选项没有任何区别.
2. 为什么执行时间差别这么大?如何避免这种情况?

我使用 Sql Server 2014.

I use Sql Server 2014.

更新:

使用统计信息:

表'CynergyResidualRecord'.扫描计数1，逻辑读1226357，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0.表工作台".扫描计数0，逻辑读0，物理读0，预读0，lob逻辑读0，lob物理读0，lob预读0.

Table 'CynergyResidualRecord'. Scan count 1, logical reads 1226357, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

推荐答案

排序是在执行键查找之前按照 CynergyResidualRecordId 的顺序获取行.

The sort is to get the rows in order of CynergyResidualRecordId prior to performing the key lookups.

这是一种优化，因此查找的随机 IO 在此讨论.

This is an optimisation so the lookups are less random IO discussed here.

对 30 万行进行排序当然不应该花费 48 秒 - 您发布的计划显示排序运算符的实际经过时间为 281 毫秒.

It certainly shouldn't take 48 seconds to sort 300K rows - the plan you posted shows the Actual elapsed Time for the sort operator is 281 ms.

实际整个计划的经过时间是 2.496 秒，所以我不确定你从哪里得到 48 秒.也许 48 秒的运行遇到阻塞，或者您的测量方法存在问题.

The actual elapsed time for the whole plan was 2.496 seconds so I'm not sure where you are getting 48 seconds from. Maybe the 48 second run encountered blocking or there is a problem with your measuring methodology.

这篇关于为什么在嵌套循环(内部联接)之前进行排序操作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！