我使用的应用程序使用 SQL Server 数据库,其中包括许多保存单行配置数据的表,在针对更传统的多行表的查询中有时需要这些表。我见过的大多数代码在处理单个查询时通过连接访问这些表,但在最近的一次代码审查中,我看到了一种使用标量子查询的方法,大致如下:
Select T.Id
From dbo.SomeTable T
Where T.SomeValue > (Select Tolerance From dbo.Settings)
虽然它显然有效,但我最初的反应是假设它违反了我们的标准做法,但我对表单进行了一些试验,发现“子查询返回超过 1 个值。当子查询如下时,这是不允许的=、!=、<、<=、>、>= 或当子查询用作表达式时”错误。这使得这似乎避免了意外 1:n 连接导致不良行为的风险。(在实践中,这些单行表不应该担心,它们相当健壮,但我已经看到它出现在系统的其他地方。)
除了(可能非常便宜)Stream Aggregate 和 Assert 之外,我的简单测试用例的执行计划看起来非常相似,我认为它们负责查询引擎在多行案例中识别和抛出错误的能力。
使用这种表格是否有普遍接受的最佳实践?在选择方法时,我应该注意哪些主要优点和缺点?
(我知道使用变量来保存数据也是一种选择,但在我们的某些代码中这样做并不总是可行的,所以我想专注于比较这两种方法和/或任何其他方式将其折叠成一个查询。)
我创建了一组演示表来看看这最终在实践中是如何工作的。
这会将 ~6,000,000 行放入 SomeTable 表中,其中 ~15,000 行大于 Settings 表中的行 (1073741823)。
来自 OP 的标量子查询版本
这导致了一种奇怪的查询计划。估计值相差了几个数量级。这导致选择并行计划。然而,由于连接的上部输入只有一行,来自下部输入的所有行最终都在一个线程上 - 导致严重偏斜(并且完全无用)的并行性。
我没有很好地解释为什么这里的估计值如此糟糕,但这对于这种方法来说似乎是一个不好的迹象,具体取决于您的表大小和数据分布。
INNER JOIN 版本
这得到了更好的估计(只有 2 倍),并且不保证并行性。
实际标量子查询
TOP (1)
我们可以通过添加到内部查询来强制子查询为标量:这导致与 INNER JOIN 版本非常相似的查询计划,其中 TOP 强制我们只从 Settings 表中获取一行。
改用参数
你提到避免这种方法,但我很好奇。如果您的应用程序代码可以缓存/检索此值,并将查询传递给它(可能在存储过程中,我在这里使用 sp_executesql):
这会产生一个很好的估计,并且通常是一个相当有效的执行计划,并且不必触及设置表。
注意:以上所有项目都依赖于 SomeTable 表的 SomeValue 列上的索引。
基于所有这些,我同意 JD 的回答,即该
INNER JOIN
方法可能更可靠。如果您被迫使用子查询方法,请查看添加是否有TOP (1)
帮助。我要补充一点,如果您可以使用参数,那就更好了,但这听起来不可行。
根据我的经验,与在子句中使用子查询相比,我通常发现使用 a来生成相同或更好的计划的更具关系性的方法。也就是说,在某些情况下,我看到在子句中使用子查询会导致执行计划不够理想。
JOIN
WHERE
WHERE
但以上不是事实或保证,只是我观察到的相关性。在极少数情况下,我也看到反过来也是如此,通过删除
JOIN
谓词并将谓词移动到WHERE
子句的子查询中,生成查询的糟糕计划执行得更好。但这我经历的次数要少得多。所以我不相信有一个永远正确的答案。您只需比较执行计划和运行时统计数据,即可查看哪个执行得更好。听起来你已经在这样做了,所以这很好。但通常我的首选是先从
JOIN
实施开始。这是另一个优点或缺点,具体取决于您的用例。正如你所说,如果你宁愿让你的代码错误(可能出乎意料的一天,似乎没有代码更改),那么这是子查询方法的一个优点。如果您的消费者可以容忍数据重复而不是出错,那么这是这种方法的一个缺点。因此,这仅取决于您的用例。