我正在尝试优化程序。该过程中存在 3 种不同的更新查询。
update #ResultSet
set MajorSector = case
when charindex(' ', Sector) > 2 then rtrim(ltrim(substring(Sector, 0, charindex(' ', Sector))))
else ltrim(rtrim(sector))
end
update #ResultSet
set MajorSector = substring(MajorSector, 5, len(MajorSector)-4)
where left(MajorSector,4) in ('(00)','(01)','(02)','(03)','(04)','(05)','(06)','(07)','(08)','(09)')
update #ResultSet
set MajorSector = substring(MajorSector, 4, len(MajorSector)-3)
where left(MajorSector,3) in ('(A)','(B)','(C)','(D)','(E)','(F)','(G)','(H)','(I)','(J)','(K)','(L)','(M)','(N)','(O)','(P)','(Q)','(R)','(S)','(T)','(U)','(V)','(W)','(X)','(Y)','(Z)')
完成所有三个更新查询只需不到10 秒。
所有三个更新查询的执行计划。
https://www.brentozar.com/pastetheplan/?id=r11BLfq7b
我的计划是将三个不同的更新查询更改为一个更新查询，以便减少 I/O。
;WITH ResultSet
AS (SELECT CASE
WHEN LEFT(temp_MajorSector, 4) IN ( '(00)', '(01)', '(02)', '(03)', '(04)', '(05)', '(06)', '(07)', '(08)', '(09)' )
THEN Substring(temp_MajorSector, 5, Len(temp_MajorSector) - 4)
WHEN LEFT(temp_MajorSector, 3) IN ( '(A)', '(B)', '(C)', '(D)','(E)', '(F)', '(G)', '(H)','(I)', '(J)', '(K)', '(L)','(M)', '(N)', '(O)', '(P)','(Q)', '(R)', '(S)', '(T)','(U)', '(V)', '(W)', '(X)','(Y)', '(Z)' )
THEN Substring(temp_MajorSector, 4, Len(temp_MajorSector) - 3)
ELSE temp_MajorSector
END AS temp_MajorSector,
MajorSector
FROM (SELECT temp_MajorSector = CASE
WHEN Charindex(' ', Sector) > 2 THEN Rtrim(Ltrim(Substring(Sector, 0, Charindex(' ', Sector))))
ELSE Ltrim(Rtrim(sector))
END,
MajorSector
FROM #ResultSet)a)
UPDATE ResultSet
SET MajorSector = temp_MajorSector
但这需要大约1 分钟才能完成。我检查了执行计划，它与第一次更新查询相同。
上述查询的执行计划：
https://www.brentozar.com/pastetheplan/?id=SJvttz9QW
有人可以解释为什么它很慢吗？
用于测试的虚拟数据：
If object_id('tempdb..#ResultSet') is not null
drop table #ResultSet
;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
SELECT CONVERT(varchar(255), NEWID()) as Sector,cast('' as varchar(1000)) as MajorSector
into #ResultSet
FROM Tally
where n <= 242906 -- my original table record count
ORDER BY n;
注意：由于这不是我的原始数据，所以我上面提到的时间可能略有不同。单个更新查询仍然比前三个慢得多。
我尝试执行查询超过 10 次以确保外部因素不会影响性能。前三个更新的所有 10 次都比最后一个更新运行得快得多。
第一个更新从表中读取和写入每一行。第二个和第三个然后重新读取和重新写入这些行的子集。看看
Actual Number of Rows。当这三个语句组合成一个时，优化器认为如果它必须读取所有内容以满足第一个更改，那么它可以为第二个和第三个更改搭载它。查看查询计划的 XML 版本，特别是
<ComputeScalar>运算符和<ScalarOperator ScalarString="">部分。在最初的计划中，您会看到每一个都相对简单并且与 SQL 的映射非常紧密。对于一体式计划，它是一个怪物。这是优化器将 SQL 重写为逻辑上等效的形式。计划通过将每一行传递给运算符一次来工作1 。当该行通过一次时，优化器正在做它必须做的所有工作以满足所有三个更改。我希望第二个查询更快，因为数据只被读取和写入一次，而在第一个中它被触及了三次。
由于第二个查询没有谓词（没有 WHERE 子句），优化器别无选择，只能读取每一行并对其进行处理。我很惊讶第二种形式比第一种形式花费的时间更长。两者都是从干净的缓冲区开始的吗？系统上是否还有其他工作正在进行？由于它正在读取和写入临时表，因此 IO 发生在 tempdb 中。是否有文件增长或类似的事情发生？
通过一种衡量标准，您已经达到了预期的结果。您说您想进行更改“以便减少 IO”。与三个单独语句的总和相比，一体机执行的 IO 更少。然而，我怀疑您真正想要的是减少经过的时间，而这显然不会发生。
1或多或少，省略了很多细节。
我运行了您的例程以生成测试数据，然后运行了三个单一更新语句和一个一体化语句。尽管存在一些差异（没有聚集索引，没有并行性），但我得到的结果或多或少是相同的。具体来说，计划的形状大致相同，三个单独的查询在大约两秒内完成，一个大查询在大约三十到三十五秒内完成。
我设置
有了缓存中的计划和内存中的数据，我得到：
我删除了一些不相关的部分。由于
physical reads所有三个表都为零，因此该表适合内存。logical reads这三个都一样，这是有道理的。由于没有索引，唯一的方法是扫描表的每一行。第二个和第三个查询影响零行，因为我已经运行了几次。CPU 时间和运行时间为 2500 毫秒。对于更大的查询，它是
读取相同数量的页面，更新相同数量的行。巨大的差异是 CPU 时间。这反映在对任务管理器的随意观察中，它显示查询执行期间的利用率为 30%。问题是，为什么要花这么多钱？
各个查询分别具有简单的计算，其中两个语句具有谓词，可以大大减少接触的行数。优化器有很好的启发式方法来处理这些并找到一个快速的计划。多合一查询应用怪物
Compute Scalar针对每一行。我的建议是，无论出于何种原因，优化器都无法将逻辑分解为可快速运行并最终使用大量 CPU 的计划。优化器必须根据给定的内容工作，在第二种情况下是复杂的嵌套 SQL。也许通过重构 SQL，优化器将遵循不同的启发式方法并获得更好的结果？也许某些（过滤后的）索引或（过滤后的）统计信息会说服它编写不同的计划。也许持久化的计算列会有所帮助。也许你只需要给优化器它需要的东西，你的第一次尝试确实是可以实现的最好的，你需要找到一种方法来并行运行这三个。对不起，我不能更科学。请以后更加小心您的测试数据。查询计划表明您的表上有一个聚集索引，但您的临时表没有聚集索引。在某些情况下，这会产生很大的不同。在我的机器上，三种
UPDATE方法在 3 秒内UPDATE运行，单一方法在 5 秒内运行。与您看到的差异并不接近，但它似乎仍然有些违反直觉。单身UPDATE不是更快吗？正如迈克尔格林在他的回答中指出的那样，这里的问题在于计算标量运算符。查询优化器不太擅长估算计算标量的成本。三个一组的第一次更新和第二次单独更新的查询计划可能看起来相同，但计算标量的工作量有很大差异。我们实际上可以获取代码并进行一些更改以将其变成有效的
SELECT查询。查询很大，完整代码在这里。下面是一个大大简化的版本：报表中所有那些重复的计算
CASE都不好。作为计算标量的一部分，SQL Server 可能会一遍又一遍地运行相同的计算。如果我只运行SELECT它大约需要 3 秒，这是第一组UPDATE查询的时间。将重复的标量计算放在
APPLY派生表中通常可以提高查询的可读性。在某些情况下，它还可以显着提高性能。APPLY我采用了那个大型查询并通过将重复的表达式移动到派生表来稍微简化它。进一步的简化是可能的，但这应该给你基本的想法：现在
SELECT查询运行不到 1 秒。我使用OUTER APPLYSQL ServerAPPLY为每一行计算一次派生表中的所有内容，而不是将其折叠到计算标量中。计算标量仍在查询计划中，但它所做的工作比以前少得多：如果我将该代码插入 CTE 以进行
UPDATE查询，我将获得以下性能数据：这比原来的三个查询集快一点：
可以进一步优化单独查询，但我会把它留给你。
在我看来，第二个和第三个查询可以使用这些公式重写：
MajorSector但是，如果没有索引（并且在临时表中，它没有索引）或者如果这些构成了表中的每一行，那并没有多大帮助。然而：
如果术语( = digit) 或( = letter) 仅出现在字符串的开头，那么您可以对所有操作：
'(0n)'n'(n)'nMajorSector但是有更多的 REPLACE (36)。
然而，这会变成
'(00)A(01)B(02)C', 变成'ABC'- 不是我们想要的。如果没有出现该数据，那么请考虑一下。如果每个都
MajorSector以'(0n)'或被'(n)'删除 - 或者根本不包含')'- 那么你真的只需要，其中
maxLength是MajorSectoreg的定义长度varchar(255)。如果长度参数 inSUBSTRING()比实际数据长，SUBSTRING()则返回从指定偏移量到字符串末尾的数据。