sql - 在插入触发器后加速 SQL Server

Question

我有一个带有表的 SQL Express 2019 服务器，我在该表上创建了一个触发器，该触发器在插入另一行时更新该表的一行。我必须每天插入大约 100,000 行。不幸的是，插入触发器大约需要 1-2 个小时，这还可以，但在测试时令人沮丧。这是我的表结构：

    [ID] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
    [REPORT_ID] BIGINT NOT NULL, 
    [CLUSTER_ID] BIGINT NOT NULL, 
    [ID_NEXT] BIGINT NULL, 
    [CREATED_AT] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    [ID_SUCCESSOR] BIGINT NULL, 
    [CREATED_AT] DATETIME NOT NULL DEFAULT GETUTCDATE(), 
    CONSTRAINT [FK_CRASHREPORTS_REPORTS2CLUSTERS_CRASHREPORTS] FOREIGN KEY ([REPORT_ID]) REFERENCES [CRASHREPORTS]([ID]),
    CONSTRAINT [FK_CRASHREPORTS_REPORTS2CLUSTERS_ID_PREV] FOREIGN KEY ([ID_NEXT]) REFERENCES [CRASHREPORTS_REPORTS2CLUSTERS]([ID]),
    CONSTRAINT [FK_CRASHREPORTS_REPORTS2CLUSTERS_ID_PREV] FOREIGN KEY ([ID_SUCCESSOR]) REFERENCES [CRASHREPORTS_REPORTS2CLUSTERS]([ID]),
    CONSTRAINT [FK_CRASHREPORTS_REPORTS2CLUSTERS_CRASHREPORTS_CLUSTERS_ID] FOREIGN KEY ([CLUSTER_ID]) REFERENCES [CRASHREPORTS_CLUSTERS]([ID])

这是我的触发器：

CREATE TRIGGER [dbo].[update_prev_after_insert]
    ON [dbo].[CRASHREPORTS_REPORTS2CLUSTERS]
    AFTER INSERT
    AS
    BEGIN
        MERGE INTO [dbo].[CRASHREPORTS_REPORTS2CLUSTERS] T
            USING inserted I
            ON T.ID != I.ID AND T.ID_SUCCESSOR is NULL and T.REPORT_ID = I.REPORT_ID
        WHEN MATCHED THEN
        UPDATE SET ID_SUCCESSOR = I.ID;
    END

一切都按预期工作，但真的很慢。有谁知道如何加快速度？

标签： sqlsql-serverdatabase-trigger