SQL Server-由于表变量而计时
#编程 #database #mssql

原始帖子,网址:https://siderite.dev/blog/sql-server---timing-out-because-of-table-variables/

不久前,我写了a post,介绍了临时表(#something)和表变量(@something)之间的差异,这些差异得出结论,在SQL Server 2019表变量糟透了之前。但是我没有给出任何真实的例子。我使它变得非常技术性和临床,也许会产生错误的印象,即这是一个微小的优化问题,而不是某人应该知道的。

所以聚集了',孩子们,让我告诉你可怕的桌子变量的故事!一个小时后,在短短3秒内使用小小的修复程序进行了时间的查询。服务器版本是... 2017年(淡入梦想序列)

首先,设置:我需要从一张大表格(30多行)中删除行,该行中没有相关的帐户在计算出的大约20万列表中。简单吗?

DECLARE @Accounts TABLE(ACC_ID CHAR(5))

INSERT INTO @Accounts
SELECT ... whatever

SELECT TOP 1 *
FROM MyTable t
LEFT OUTER JOIN @Accounts a
ON t.ACC_ID=a.ACC_ID
WHERE a.ACC_ID IS NULL

请给我第一个孤儿记录... ... ... 40分钟后... WTF?

正如我在the previous post中所说的那样,表变量没有统计数据,并且发动机假设它们只有一排。那么执行计划需要什么?要浏览所有30百万行的所有行,然后将其搜索到会计表中。只有会计表也有20,000行。它需要永远!

请注意,我在这里简化了这种情况。表变量确实允许索引和主键。我尝试了。根本没有效果!

让我们更改查询:

CREATE TABLE #Accounts(ACC_ID CHAR(5))

INSERT INTO #Accounts
SELECT ... whatever

SELECT TOP 1 *
FROM MyTable t
LEFT OUTER JOIN #Accounts a
ON t.ACC_ID=a.ACC_ID
WHERE a.ACC_ID IS NULL

DROP TABLE #Accounts

就给我...等等,什么?查询在3秒内结束。突然,知道表中的行的数量和结构导致了正确的执行计划。

但是我不能以某种方式使用表变量吗?您可以,但是您必须强迫发动机以自己的方式进行操作。首先,您使用的是OPTION (FORCE ORDER),该OPTION (FORCE ORDER)将按照您声明的顺序保持表格。然后您必须逆转加入,以使@Accounts是第一张表,但是效果是相同的。

DECLARE @Accounts TABLE(ACC_ID CHAR(5))

INSERT INTO @Accounts
SELECT ... whatever

SELECT TOP 1 *
FROM @Accounts a
RIGHT OUTER JOIN MyTable t
ON t.ACC_ID=a.ACC_ID
WHERE a.ACC_ID IS NULL
OPTION (FORCE ORDER)

回到三秒钟。 gh!

现在,我可能会使用临时表,因为强迫SQL引擎解释您的查询几乎总是一个坏主意,并且它也使代码更难阅读。

希望它有帮助!