一张大桌子有很多更新.如何加速? [英] Lots of updates to a large table. How to speed up?

问题描述

我有一个大表(60 列，200 万行).

I have a large table (60 columns, 2 million rows).

它首先通过递归查询构建，然后大多数列以自己的方式更新.所有这些更新都非常缓慢(占全球持续时间的 80%).

It is built by first a recursive query, and then most columns get updated in their own way. All this updating is quite slow (80% of global duration).

更新的顺序不能完全随机，因为某些列用作其他列计算的输入.但是订单在某种程度上是免费的.

The order of the updates cannot be completely random as some columns serve as input for the computation of other columns. But the order is free to some extent.

用大的 SELECT CASE 替换 UPDATE 列表是常见的做法吗?目前我有这样的事情:

Is it common practice to replace a list of UPDATE by a large SELECT CASE ? Currently I have something like this:

UPDATE t SET col1=col2/col3 WHERE col4 IS NULL AND col5 IS NOT NULL; --UPDATE Nr1

UPDATE t SET col23=col24+col25 WHERE col26 IS NULL; --UPDATE Nr2

...

UPDATE t SET col46=col47*col48 WHERE col1 IS NULL --UPDATE Nr50

可以用类似的东西代替:

Could it be replaced by something like:

CREATE TABLE t2 AS
SELECT
CASE WHEN col4 IS NULL AND col5 IS NOT NULL THEN col2/col3 ELSE col1 END AS col1,
...
CASE WHEN col26 IS NULL THEN col24+col25 ELSE col23 END AS col23,
..
FROM t;

CREATE TABLE t3 AS
SELECT
col1,
col2,...,
CASE WHEN col1 IS NULL THEN col47*col48 ELSE col46 END AS col46
FROM t2;

推荐答案

--Requete 40. Performance cost: 3%
UPDATE #temp_arbo_of_3
SET PxAchat=NULL, CuTpsAch=NULL
WHERE IdBE IS NULL;


--Requete 41. Performance cost: 2%
UPDATE #temp_arbo_of_3
SET CuTrait = NULL
WHERE IdBE IS NOT NULL;


--Requete 42. Performance cost: 2%
UPDATE #temp_arbo_of_3
SET NrOF_Source = _ofI
WHERE IdBE IS NOT NULL;

现在，如果我将所有这些替换为:

Now if I replace all this by:

--Requete 40. Performance cost: 3%
UPDATE #temp_arbo_of_3
SET PxAchat=CASE WHEN IdBE IS NULL THEN NULL ELSE PxAchat END,
     CuTpsAch=CASE WHEN IdBE IS NULL THEN NULL ELSE CuTpsAch END,
     CuTrait=CASE WHEN IdBE IS NOT NULL THEN NULL ELSE CuTrait END,
     NrOF_Source=CASE WHEN IdBE IS NOT NULL THEN _ofI ELSE NrOF_source END
WHERE IdBE IS NULL;

性能(如 SQL Server 执行计划所示)更好.3%+2%+2% > 3%

Performance (as indicated by SQL Server execution plan) is better. 3%+2%+2% > 3%

这篇关于一张大桌子有很多更新.如何加速?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！