更新表中的每一行数据。

Question

更新表中的每一行数据。

5

我有一个表格，其中包含一些数值列、公式和结果列。

|rownum|value1|value2|value3|formula             |result|
|------|------|------|------|--------------------|------|
|1     |11    |30    |8     |value1/value2*value3|      |
|2     |43    |0     |93    |value1-value2+value3|      |

我希望将 result 列填充为公式的结果。

目前，我正在使用以下查询完成此操作:

DECLARE @v_sql NVARCHAR(MAX) 

SET @v_Sql = CAST ((SELECT
           ' UPDATE [table] ' +
            ' SET [result] = ' + table.[formula] +
            ' WHERE [rownum] = ' + CAST(table.[rownum] as nvarchar(255)) +
            ';'
          FROM [table]
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') AS NVARCHAR (MAX))

EXEC (@v_Sql)

问题在于这需要很长时间。表中的行数将达到500万至1000万。

有没有办法加快速度？或者有其他解决方法吗？

非常感谢！

- tv87

你可能想在Code Review上发布这个。那是一个更好的地方，适合你想要加速的工作代码。 - Egan Wolf

公式列是否为计算列？ - ahmed abdelqader

看起来你将在未来使用这个结果列，你没有使用触发器或其他东西在数据插入时运行该公式，所以你可以在一个查询中解析公式并将其从字符串解码为实际过程。我会尝试一下查询。 - Suleyman DOGAN

1

可能是，也可能不是。但很可能的问题是结果列应该已经包含了函数的结果。不知道这是否是您想要运行的一次性更新系列。如果不是，您应该在公式列更改或插入时立即初始化结果列。 - TT.

1

@tv87 您可以创建一个“AFTER INSERT/UPDATE TRIGGER”，重新评估该公式并将结果放入结果列中。这样，您就不必每次都评估整个表格，只需为插入或更新的行进行评估即可。我不认为您每次都更新/插入500万/1000万行。 - TT.

显示剩余3条评论

6个回答

2

通过公式类型进行批量更新是否更快？需要在[公式]上建立索引：

DECLARE @v_sql NVARCHAR(MAX) 

SET @v_Sql = CAST ((SELECT
           ' UPDATE [table] ' +
            ' SET [result] = ' + [table].[formula] +
            ' WHERE [formula] = ''' + [table].[formula] + ''';'
          FROM [table]
          group by [table].[formula]
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') AS NVARCHAR (MAX))
exec(@v_Sql)

- cloudsafe

1

我能想到的两个简单方法：

如果您要逐行更新，请确保 rownum 列上有索引。
如果只有少量不同的公式，您可以在一个 UPDATE 中使用相同的公式更新所有行，而不是逐行更新。在这种情况下，formula 列上的索引会有所帮助。

- Vladimir Baranov

嗨，我在rownum列上添加了索引，这加快了查询速度。不幸的是，它仍然太慢了。使用的不同公式数量有限，并取决于与此表相关联的维度。因此，对于100万行，可能有100个唯一的公式需要应用。我将尝试将公式移动到此维度表中，并从那里检索以限制必须执行的更新语句的数量。 - tv87

@tv87，运行100次UPDATE肯定比100万次快。特别是当更新的总行数相同时。速度应该快近10000倍。不过，在formula列上创建索引非常重要。 - Vladimir Baranov

0

感谢所有的回复和想法。最终问题是通过将公式保存在维度而不是事实表中解决的。这为每个维度行生成1个更新语句，并将其应用于具有where子句的所有相关事实行，而不是每个事实行生成1个更新语句。处理时间从> 1.5小时降至不到一秒钟。

- tv87

0

选择触发器选项，但目前来说，分批更新将会产生较小的影响。

TOP(5000) 每次只会更新5000行数据 WHERE [result] is null OR [result]=''

GO 20000 将执行此查询20000次（1000万行数据）。它将继续执行，直到UPDATE语句返回0条记录为止。

DECLARE @v_sql NVARCHAR(MAX) 

SET @v_Sql = CAST ((SELECT
           ' UPDATE TOP (5000) [table] ' +
            ' SET [result] = ' + [table].[formula] +
            ' WHERE [formula] = ''' + [table].[formula] + '''
             AND ([result] is null OR [result]='');'
          FROM [table]
          group by [table].[formula]
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') AS NVARCHAR (MAX))
exec(@v_Sql)

    GO 20000

接下来，创建触发器

- Horaciux

0

我刚刚创建了一个有 500 万行的表格。该表格的结构如下：

rn  t1  t2  t3  formula
1   80  23  93  t1 / t2 * t3
2   80  87  30  t1 / t2 * t3
3   92  83  63  t1 / t2 * t3
4   68  19  36  t1 / t2 * t3
5   65  63  10  t1 / t2 * t3

如果您确信所有的公式都是有效的，例如不会出现零除或数据类型溢出等情况，那么您可以在 SQL Server 中创建自己的 eval() 函数。

我为带有符号的三个值的公式创建了自己的函数，这些符号包括：“+”，“-”，“*”，“/”。

函数代码如下：

use db_test;
go

alter function dbo.eval(@a varchar(max))
returns float
as
begin
    set @a = replace(@a, ' ', '');

    declare @pos1 int = PATINDEX('%[+/*-]%', @a);
    declare @t1 float = cast(substring(@a, 1, @pos1 - 1) as float);
    declare @sign1 char(1) = substring(@a, @pos1, 1);
    set @a = substring(@a, @pos1 + 1, len(@a) - @pos1);

    declare @pos2 int = PATINDEX('%[+/*-]%', @a);
    declare @t2 float = cast(substring(@a, 1, @pos2 - 1) as float);
    declare @sign2 char(1) = substring(@a, @pos2, 1);
    set @a = substring(@a, @pos2 + 1, len(@a) - @pos2);

    declare @t3 float = cast(@a as float);

    set @t1 = (
        case @sign1
            when '+' then @t1 + @t2
            when '-' then @t1 - @t2
            when '*' then @t1 * @t2
            when '/' then @t1 / @t2
        end 
    );

    set @t1 = (
        case @sign2
            when '+' then @t1 + @t3
            when '-' then @t1 - @t3
            when '*' then @t1 * @t3
            when '/' then @t1 / @t3
        end 
    );

    return @t1;
end;

它适用于下一个数据：

select dbo.eval('7.6*11.3/4.5') as eval, 7.6*11.3/4.5 as sqlServerCalc;

eval                 sqlServerCalc
19,0844444444444     19.084444

接下来，您可以通过列值替换公式中的值并进行计算：

with cte as (
    select rn, t1, t2, t3, formula, 
        REPLACE(REPLACE(REPLACE(formula, 't1', cast(t1 as varchar(max))), 't2', cast(t2 as varchar(max))), 't3', cast(t3 as varchar(max))) as calc
    from db_test.dbo.loop
)
select rn, t1, t2, t3, formula, db_test.dbo.eval(calc) as result
into db_test.dbo.loop2
from cte;

时间对我来说还可以，我的Sql Server 2016上只需要3分钟就能得到很好的结果：

select top 5 *
from db_test.dbo.loop2;
rn  t1  t2  t3  formula         result
1   80  23  93  t1 / t2 * t3    323,478260869565
2   80  87  30  t1 / t2 * t3    27,5862068965517
3   92  83  63  t1 / t2 * t3    69,8313253012048
4   68  19  36  t1 / t2 * t3    128,842105263158
5   65  63  10  t1 / t2 * t3    10,3174603174603

如果您有一个适用于公式的所有操作列表，那么可以为多个变量编写通用函数。但是，如果公式中存在更复杂的内容，则应使用CLR。

- Evgeny

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- cloudsafe · Accepted Answer

假设运算符顺序规则，仅涵盖您的简单公式示例：

UPDATE [table]
SET  [result] = case replace(replace(replace([formula],'value1', ''), 'Value2', ''), 'Value3', '')
        when '++' then [value1] + [value2] + [Value3]
        when '+-' then [value1] + [value2] - [Value3]
        when '+*' then [value1] + [value2] * [Value3]
        when '+/' then [value1] + [value2] / [Value3]
        when '-+' then [value1] - [value2] + [Value3]
        when '--' then [value1] - [value2] - [Value3]
        when '-*' then [value1] - [value2] * [Value3]
        when '-/' then [value1] - [value2] / [Value3]
        when '*+' then [value1] * [value2] + [Value3]
        when '*-' then [value1] * [value2] - [Value3]
        when '**' then [value1] * [value2] * [Value3]
        when '*/' then [value1] * [value2] / [Value3]
        when '/+' then [value1] / [value2] + [Value3]
        when '/-' then [value1] / [value2] - [Value3]
        when '/*' then [value1] / [value2] * [Value3]
        when '//' then [value1] / [value2] / [Value3]
        end
from [Table]