避免游标使用触发器更新许多记录 [英] Avoiding cursors to update many records using a trigger

问题描述

我有一个表，只有超过100万条记录。最初，我的表是空的，但我使用 BULK INSERT 将这些记录添加到数据库。我有一个 AFTER INSERT 触发器，用于更新此表中的 initialValue 字段。 initialValue 的值是对所有记录求和的另一个表（ my_data_db ）中的特定变量的计算。我使用 v1 ， v2 等的列作为表中的列名 my_data_db 。

I have a table with just over 1 million records. Initially, my table is empty, but I use a BULK INSERT to add these records to the database. I have an AFTER INSERT trigger that I use to update the initialValue field in this table. The value of initialValue is a calculation of specific variables in another table (my_data_db) summed across all records. I am using the values of the v1, v2, etc. columns as the column names in the table my_data_db.

我知道这是不好的做法，但我目前知道如何做每行的计算的唯一方法是通过使用光标。显然有一百万条记录，这是真的，真的很慢。

I know it's poor practice, but the only way I currently know how to do this calculation of every single row is by using a cursor. Obviously with a million records this is really, really slow.

这是一个表的例子，我有触发器：

Here's an example of the table that I have the trigger on:

TABLE: test3
rowID    v1      v2      v3       combo   initialValue
1        NULL    M170_3  M170_4   C       NULL
2        M170_2  M170_3  M170_4   ABC     NULL
3        M170_2  M170_3  NULL     AB      NULL
...

我的触发器是：

CREATE TRIGGER [dbo].[trig_UPDATE_test3] 
ON [dbo].[test3] 

AFTER INSERT 
AS 
Begin 

DECLARE @sql VARCHAR(MAX) 
DECLARE @v1 VARCHAR(20) 
DECLARE @v2 VARCHAR(20) 
DECLARE @v3 VARCHAR(20) 
DECLARE @combo VARCHAR(30) 
DECLARE mycursor CURSOR FOR     
    SELECT v1, v2, v3, combo 
    FROM Inserted 

    OPEN mycursor 
    FETCH NEXT FROM mycursor INTO @v1, @v2, @v3, @combo 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        IF( @v1 IS NOT NULL OR @v2 IS NOT NULL OR @v3 IS NOT NULL) 
        BEGIN 
            SET @sql = 'DECLARE @finalValue DECIMAL(18, 15);' 
            SET @sql = @sql + 'UPDATE test3 Set initialValue = (SELECT CAST(SUM(' 

            IF(@v1 IS NOT NULL) 
            BEGIN 
                SET @sql = @sql + 'CASE ' + @v1 + ' WHEN 1 THEN 1 WHEN 2 THEN .75 WHEN 3 THEN .25 WHEN 4 THEN .1 END * ' 
            END 

            IF(@v2 IS NOT NULL) 
            BEGIN 
                SET @sql = @sql + 'CASE ' + @v2 + ' WHEN 1 THEN 1 WHEN 2 THEN .75 WHEN 3 THEN .25 WHEN 4 THEN .1 END * ' 
            END 

            IF(@v3 IS NOT NULL) 
            BEGIN 
                SET @sql = @sql + 'CASE ' + @v3 + ' WHEN 1 THEN 1 WHEN 2 THEN .75 WHEN 3 THEN .25 WHEN 4 THEN .1 END * ' 
            END 

            SET @sql = @sql + 'RESP_WEIGHT / 4898.947426) AS FLOAT) FROM dbo.my_data_db) WHERE combo = ''' + @combo + ''';' 

            EXECUTE(@sql) 

        END 
        FETCH NEXT FROM mycursor INTO @v1, @v2, @v3, @combo 
    END 
    CLOSE mycursor 
    DEALLOCATE mycursor 
End

触发器运行后，我的 test3 表将如下所示：

After the trigger runs, my test3 table would look something like:

TABLE: test3
rowID    v1      v2      v3       combo   initialValue
1        NULL    M170_3  M170_4   C       0.138529
2        M170_2  M170_3  M170_4   ABC     0.683190
3        M170_2  M170_3  NULL     AB      0.014923
...

有没有一种方法，我可以完成这个而不使用光标？

Is there a way I can accomplish this without the use of a cursor?

推荐答案

是的。您可以在 BULK INSERT 之后使用单个 UPDATE - FROM 语句来执行此操作：

Yes. You can do it with a single UPDATE - FROM statement after your BULK INSERT:

UPDATE t3 SET initialValue = t.mySum
FROM test3 t3
CROSS APPLY (SELECT SUM(
       CASE t3.v1 WHEN 'M170_2' THEN CASE d.M170_2 
             WHEN 1 THEN 1 
             WHEN 2 THEN .75 
             WHEN 3 THEN .25 
             WHEN 4 THEN .1 
       ELSE 1 END END * 
       CASE t3.v1 WHEN 'M170_3' THEN CASE d.M170_3 
             WHEN 1 THEN 1 
             WHEN 2 THEN .75 
             WHEN 3 THEN .25 
             WHEN 4 THEN .1 
       ELSE 1 END END * 
       CASE t3.v1 WHEN 'M170_4' THEN CASE d.M170_4 
             WHEN 1 THEN 1 
             WHEN 2 THEN .75 
             WHEN 3 THEN .25 
             WHEN 4 THEN .1 
       ELSE 1 END END * 
       d.RESP_WEIGHT / 4898.947426) as mySum 
       FROM my_data_db d WHERE d.combo = t3.combo) t
WHERE t3.v1 IS NOT NULL OR t3.v2 IS NOT NULL OR t3.v3 IS NOT NULL

要从触发器执行此操作，您需要进行细微的更改：

To do this from your trigger, you need a minor change:

UPDATE t3 SET initialValue = t.mySum
FROM test3 t3
-- Here's the change
INNER JOIN inserted i ON i.RowID = t3.RowID
CROSS APPLY (SELECT SUM(
       CASE t3.v1 WHEN 'M170_2' THEN CASE d.M170_2 
             WHEN 1 THEN 1 
             WHEN 2 THEN .75 
             WHEN 3 THEN .25 
             WHEN 4 THEN .1 
       ELSE 1 END END * 
       CASE t3.v1 WHEN 'M170_3' THEN CASE d.M170_3 
             WHEN 1 THEN 1 
             WHEN 2 THEN .75 
             WHEN 3 THEN .25 
             WHEN 4 THEN .1 
       ELSE 1 END END * 
       CASE t3.v1 WHEN 'M170_4' THEN CASE d.M170_4 
             WHEN 1 THEN 1 
             WHEN 2 THEN .75 
             WHEN 3 THEN .25 
             WHEN 4 THEN .1 
       ELSE 1 END END * 
       d.RESP_WEIGHT / 4898.947426) as mySum 
       FROM my_data_db d WHERE d.combo = t3.combo) t
WHERE t3.v1 IS NOT NULL OR t3.v2 IS NOT NULL OR t3.v3 IS NOT NULL

这篇关于避免游标使用触发器更新许多记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！