SQL TOP 5000 比结果行数少于 5000 的普通查询快？

Question

我注意到一些奇怪的行为:

运行此查询:

SELECT TOP 5000  t1.f1,t1.f2,t1.f3 
FROM t1
JOIN t2 on t1.f1 = t2.f1
WHERE t2.f1 IS NOT NULL AND (t1.f5 != t2.f3)

2 秒内生成 3447 行结果。

运行这个:

SELECT t1.f1,t1.f2,t1.f3 
FROM t1
JOIN t2 on t1.f1 = t2.f1
WHERE t2.f1 IS NOT NULL AND (t1.f5 != t2.f3)

永远运行，直到我停止它(至少 120 分钟!!)。

表 t1 和 t2 保存大约 500k 条记录。

我一直认为如果总行数低于该数字，TOP 语句并不重要，但是，似乎存在非常显着的差异。这是正常现象(如果是，为什么)还是只是侥幸？

编辑:

根据要求:

t1:

CREATE TABLE [dbo].[t1](
    [f1] [int] NOT NULL,
    [f2] [varchar](10) NULL,
    [f3] [varchar](4) NULL,
    [f4] [int] NOT NULL,
    [f5] [varchar](max) NULL,
 CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED 
(
    [f1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

f2:

CREATE TABLE [dbo].[t2](
    [f1] [nchar](10) NOT NULL,
    [f2] [nchar](10) NOT NULL,
    [f3] [varchar](max) NOT NULL,
    [f4] [nchar](10) NULL,
    [f5] [date] NULL,
    [f6] [date] NULL,
    [f7] [nchar](1) NULL,
 CONSTRAINT [PK_t2] PRIMARY KEY CLUSTERED 
(
    [f1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

执行计划:

带顶部:

没有顶部:

看到这一点，我不得不得出结论，排序(为什么这样做？？)导致了延迟......你同意吗？

编辑2:根据要求，执行计划带有循环选项，没有顶部:

Answer 1

问题在于，您的两个表 [t1] 和 [t2] 对于 JOIN 列 f1 具有完全不同(且很大程度上不兼容)的数据类型。

这使得查询优化器无法准确估计这两个 500,000 行表之间将匹配的行数。它似乎使用了默认的“猜测”，在本例中是对实际数字 (3477) 的粗略估计。因此，当您不使用 TOP 时，它认为对行进行排序然后合并 (O(NLogN)) 比执行嵌套循环 (O(N^2)) 更有效，因为它确实没有意识到(合并)JOIN 实际上会消除几乎所有的行。

当你打开 TOP 5000 时，它会意识到嵌套循环更好，因为它会在不超过 5000 时被切断(远小于 500k^2，甚至小于 500k * Log(500k) ) 。但与嵌套循环不同的是，合并排序不能增量完成，它必须首先对所有行进行排序。因此，在 5000 处切断输出根本不会为您节省太多，因此嵌套循环显然是更好的选择(即使 JOIN 估计不好)。

<小时/>

这里的根本问题是列 T2.f1 是一个 NCHAR(10)，对于看起来应该包含整数的内容来说，这是一个非常糟糕的选择。最好的解决方案是将该列的数据类型更改为 INT。

如果由于某种原因你不能这样做，那么根据你的 SQL Server 版本，你可以通过添加一个持久计算列来结束运行，该计算列计算 [f1] 的 INT 转换值，然后抛出一个兼容的索引那个。对于这样的查询，这将允许索引和统计再次工作。

作为最后的手段，您还可以使用查询提示。我通常不推荐它们，因为它们往往是权宜之计，会在以后引起问题。但是，如果您认为这是唯一的选择，那么在查询末尾添加 OPTION (FAST 1000) 可能会起作用。