SQL Server - Обновление данных с использованием дат EffectiveFrom и EffectiveTo - Оптимизация Upsert

У нас есть требование создать таблицу, которая поддерживает временную достоверность (т.е. для данного ключа, в данном случае Md5 в таблице ниже, не будет перекрывающихся периодов). Пользователям нужна возможность устанавливать даты EffectiveFrom и EffectiveTo, поэтому темпоральные таблицы бесполезны, поскольку они кажутся только для разрешения сгенерированных системой дат. Сценарий использования состоит в том, что массовые данные будут загружены с установленным диапазоном дат действительности, и это необходимо применить к существующим данным, чтобы гарантировать отсутствие перекрытия периодов.

Определения таблиц:

IF OBJECT_ID('dbo.IngestedData', 'U') IS NOT NULL
    DROP TABLE IngestedData;

CREATE TABLE IngestedData
(   
    ID INT IDENTITY(1,1),
    Md5 VARCHAR(15) NOT NULL,   
    EffectiveFrom DATE NOT NULL,    
    EffectiveTo DATE NOT NULL,
    UpdateUser VARCHAR(50),
    JsonData VARCHAR(MAX),
    CONSTRAINT CK_IngestedData_Start_End CHECK (EffectiveFrom < EffectiveTo),
    CONSTRAINT UK_IngestedData_Md5_Start_End UNIQUE(Md5, EffectiveFrom),
    PRIMARY KEY (Id)
);

CREATE NONCLUSTERED INDEX AK_IngestedData_Md5 
   ON IngestedData (Md5); 
CREATE NONCLUSTERED INDEX AK_IngestedData_EffectiveFrom   
   ON IngestedData (EffectiveFrom);
CREATE NONCLUSTERED INDEX AK_IngestedData_EffectiveTo 
   ON IngestedData (EffectiveTo);

Я написал процедуру upsert, которая хорошо работает для обновлений одной строки, а именно:

Процедура обновления:

CREATE PROCEDURE dbo.usp_UpsertIngestedDataRow 
    @Md5 VARCHAR(20),
    @EffectiveFrom DateTime,
    @EffectiveTo DateTime,
    @UpdateUser VARCHAR(50),
    @JsonData VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY;
    BEGIN TRANSACTION;
        --Select the data that needs to be modified along with the action to be taken
        WITH NewRow(ID, Md5, EffectiveFrom, EffectiveTo, UpdateUser, JsonData, [Action]) AS
        (
            SELECT NULL, @Md5, @EffectiveFrom, @EffectiveTo, @UpdateUser, @JsonData, 'I'
        ),
        OverlappingRows(ID, Md5, EffectiveFrom, EffectiveTo, UpdateUser, JsonData) AS
        (
            SELECT 
                X.ID, X.Md5, X.EffectiveFrom, X.EffectiveTo, X.UpdateUser, X.JsonData 
            FROM 
                NewRow A 
            JOIN 
                IngestedData X ON (X.EffectiveFrom < A.EffectiveTo
                               AND X.EffectiveTo > A.EffectiveFrom)
                               AND A.Md5 = X.Md5
        ),
        NewStartRows(ID, Md5, EffectiveFrom, EffectiveTo, UpdateUser, JsonData, [Action]) AS
        (
            SELECT 
                s.ID, s.Md5, s.EffectiveFrom, 
                (SELECT DATEADD(DAY, -1, MIN(EffectiveFrom)) 
                 FROM NewRow), 
                s.UpdateUser, s.JsonData, 'I'
            FROM
                OverlappingRows s
            WHERE 
                EffectiveFrom < (SELECT MIN(EffectiveFrom) FROM NewRow)
        ),
        NewEndRows(ID, Md5, EffectiveFrom, EffectiveTo, UpdateUser, JsonData, [Action]) AS
        (
            SELECT 
                s.ID, s.Md5, 
                (SELECT DATEADD(DAY, 1, MIN(EffectiveTo)) 
                 FROM NewRow), 
                s.EffectiveTo, s.UpdateUser, s.JsonData, 'I'
            FROM  
                OverlappingRows s
            WHERE 
                EffectiveTo > (SELECT MAX(EffectiveTo) FROM NewRow)
        ),
        DeleteRows(ID, Md5, EffectiveFrom, EffectiveTo, UpdateUser, JsonData, [Action]) AS
        (
            SELECT 
                del.ID, del.Md5, del.EffectiveFrom, del.EffectiveTo, 
                del.UpdateUser, del.JsonData, 'D'
            FROM
                OverlappingRows del 
            INNER JOIN 
                NewRow n ON n.EffectiveFrom <= del.EffectiveFrom 
                         AND n.EffectiveTo >= del.EffectiveTo
        )
        SELECT *
        INTO #Temp
        FROM
            (SELECT * FROM NewRow
             UNION
             SELECT * FROM NewStartRows
             UNION
             SELECT * FROM NewEndRows
             UNION
             SELECT * FROM DeleteRows) AS Data;

        --Delete any rows that are being replaced
        DELETE FROM IngestedData WHERE ID IN (SELECT DISTINCT ID FROM #Temp)

        --Insert the replacement
        INSERT INTO IngestedData(Md5, EffectiveFrom, EffectiveTo, UpdateUser, JsonData) 
        SELECT Md5, EffectiveFrom, EffectiveTo, UpdateUser, JsonData
        FROM #Temp
        WHERE [Action] = 'I'

        --Drop temp table
        IF  OBJECT_ID('tempdb.dbo.#Temp', 'U') IS NOT NULL
            DROP TABLE #Temp

    COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH


END
GO

Производительность отдельного вызова хорошая: время вызова составляет около 7 мс, даже когда таблица заполнена 10 000 000 записей. Проблема заключается в большом количестве обновлений. Выполнение указанной выше хранимой процедуры для 35 000 записей с помощью курсора занимает около 5 минут.

Я попытался переписать процедуру так, чтобы она принимала табличную переменную, которая позволяла бы DML использовать операции над наборами, но потерялась в логике. Может ли кто-нибудь помочь превратить приведенную выше логику в обновление на основе набора, которое следует этому шаблону:

Новая хранимая процедура:

CREATE PROCEDURE usp_BulkUpsertIngestedData 
    @UpdateUser VARCHAR(15), 
    @NewRows DataIngestionRecord READONLY
AS
BEGIN

Определение типа

CREATE TYPE DataIngestionRecord AS TABLE
            (
                Md5 VARCHAR(15) NOT NULL,   
                EffectiveFrom DATE NOT NULL,    
                EffectiveTo DATE NOT NULL,
                JsonData VARCHAR(MAX)
            )