How can I speed up this SQL Server CURSOR used for change tracking? [closed]

Solution 1:

Acknowledging the recommendation for using change data tracking and caution against putting too much logic into triggers, the following is a refactoring (and some outright rewriting) of your change capture logic.

The updated logic makes a single pass through the data, handing all affected records at once. Given the requirements, I think it is pretty close to optimal, but there may still be room for improvements. The conversion to and from XML likely adds a significant bit of overhead. The alternative would be to dynamically generate and apply custom triggers for each table that explicitly reference all of the data columns individually to get the details and UNION them together.

I also refined the value comparison to better handle nulls, case sensitivity, and potential trailing space changes.

The code below is not in the form of a trigger, but in a form suitable for stand-alone testing. I figured you (and any others who may be interested) would want to test Once checked out, you should be able to retrofit it back into your trigger.

Note that this is not a 100% generalized solution. Some column types may not be supported. The logic currently assumes a single column primary key of type integer. Changes would be required to handle deviations from these (and possibly some currently unidentified) constraints.

-- Simulated change log tables
DECLARE @TableChangeLogSummary TABLE (ID INT IDENTITY(1,1), KeyValue INT NOT NULL, ModifiedDate DATETIME NOT NULL, ChangeType CHAR(1) NOT NULL, TableName NVARCHAR(1000) NOT NULL )
DECLARE @TableChangeLogDetails TABLE (ID INT IDENTITY(1,1), SummaryID int NOT NULl, ColumnName NVARCHAR(1000) NOT NULL, OldValue NVARCHAR(MAX), NewValue NVARCHAR(MAX))

-- Simulated system defined inserted/deleted tables
DECLARE @inserted TABLE (ID INTEGER, Value1 NVARCHAR(100), Value2 BIT, Value3 FLOAT)
DECLARE @deleted TABLE (ID INTEGER, Value1 NVARCHAR(100), Value2 BIT, Value3 FLOAT)

-- Test data
INSERT @inserted
VALUES
    (1, 'AAA', 0, 3.14159), -- Insert
    (2, 'BBB', 1, null),    -- Mixed updates including null to non-null and non-null to null
    (3, 'CCC', 0, 0),       -- Trailing space change
    (4, 'DDD', null, 1.68), -- No changes
    (5, '', 0, null),       -- No changes with blanks and nulls
    (6, null, null, null),  -- No changes all nulls
    (7, null, null, null)   -- Insert all nulls (summary with key, but no details will be logged)

INSERT @deleted
VALUES
    (2, 'bbb', null, 2.73),
    (3, 'CCC ', 0, 0),
    (4, 'DDD', null, 1.68),
    (5, '', 0, null),
    (6, null, null, null),
    (8, null, null, null), -- Delete all null values (summary with key, but no details will be logged)
    (9, 'ZZZ', 999, 999.9) -- Delete non-nulls

--- Now the real work begins...

-- Set table and information. Assumes table has exactly one PK column. Later logic assumes an INT.
DECLARE @TableName NVARCHAR(1000) = 'MyTable' -- To be extracted from the parent object of the trigger
DECLARE @KeyColumnName SYSNAME = 'ID' -- This can be fixed if known or derived on the fly from the primary key definition

-- Extract inserted and/or deleted data
DECLARE @InsertedXml XML = (
        SELECT *
        FROM @inserted
        FOR XML PATH('inserted'), TYPE
)
DECLARE @DeletedXml XML = (
        SELECT *
        FROM @deleted
        FOR XML PATH('deleted'), TYPE
)

-- Parse and reassange the captured key and data values
DECLARE @TempDetails TABLE(
    KeyValue INT NOT NULL,
    ChangeType CHAR(1) NOT NULL,
    ColumnName VARCHAR(1000) NOT NULL,
    IsKeyColumn BIT NOT NULL,
    NewValue NVARCHAR(MAX),
    OldValue NVARCHAR(MAX))
INSERT @TempDetails
SELECT
    KeyValue = COALESCE(I.KeyValue, D.KeyValue),
    ChangeType = CASE WHEN D.KeyValue IS NULL THEN 'I' WHEN I.KeyValue IS NULL THEN 'D' ELSE 'U' END,
    ColumnName = COALESCE(I.ColumnName, D.ColumnName),
    IsKeyColumn = K.IsKeyColumn,
    NewValue = I.Value,
    OldValue = D.Value
FROM (
    SELECT K.KeyValue, C.ColumnName, C.Value
    FROM @InsertedXml.nodes( '/inserted' ) R(Row)
    CROSS APPLY (
        SELECT KeyValue = C.Col.value('text()[1]', 'int')
        FROM R.Row.nodes( './*' ) C(Col)
        WHERE C.Col.value( 'local-name(.)', 'nvarchar(MAX)' ) = @KeyColumnName
    ) K
    CROSS APPLY (
        SELECT ColumnName = C.Col.value('local-name(.)', 'nvarchar(MAX)'), Value = C.Col.value('text()[1]', 'nvarchar(MAX)')
        FROM R.Row.nodes( './*' ) C(Col)
    ) C
) I
FULL OUTER JOIN (
    SELECT K.KeyValue, C.ColumnName, C.Value
    FROM @DeletedXml.nodes( '/deleted' ) R(Row)
    CROSS APPLY (
        SELECT KeyValue = C.Col.value('text()[1]', 'int')
        FROM R.Row.nodes( './*' ) C(Col)
        WHERE C.Col.value( 'local-name(.)', 'nvarchar(MAX)' ) = @KeyColumnName
    ) K
    CROSS APPLY (
        SELECT ColumnName = C.Col.value('local-name(.)', 'nvarchar(MAX)'), Value = C.Col.value('text()[1]', 'nvarchar(MAX)')
        FROM R.Row.nodes( './*' ) C(Col)
    ) C
) D
    ON D.KeyValue = I.KeyValue
    AND D.ColumnName = I.ColumnName
CROSS APPLY (
    SELECT IsKeyColumn = CASE WHEN COALESCE(I.ColumnName, D.ColumnName) = @KeyColumnName THEN 1 ELSE 0 END
) K
WHERE ( -- We need to be careful about edge cases here
    (I.Value IS NULL AND D.Value IS NOT NULL)
    OR (I.Value IS NOT NULL AND D.Value IS NULL)
    OR I.Value <> D.Value COLLATE Latin1_General_Bin -- Precise compare (case and accent sensitive)
    OR DATALENGTH(I.Value) <> DATALENGTH(D.Value) -- Catch trailing space cases
    OR K.IsKeyColumn = 1
    )

-- Get rid of updates with no changes, but keep key-only inserts or deletes
DELETE T
FROM @TempDetails T
WHERE T.IsKeyColumn = 1
AND T.ChangeType = 'U'
AND NOT EXISTS (
    SELECT *
    FROM @TempDetails T2
    WHERE T2.KeyValue = T.KeyValue
    AND T2.IsKeyColumn = 0
)

-- Local table to capture and link SummaryID between the summary and details tables
DECLARE @CaptureSummaryID TABLE (SummaryID int, KeyValue INT NOT NULL)

-- Insert change summary and capture the assigned Summary ID via the OUTPUT clause
INSERT INTO @TableChangeLogSummary (KeyValue, ModifiedDate, ChangeType, TableName)
OUTPUT INSERTED.id, INSERTED.KeyValue INTO @CaptureSummaryID
SELECT T.KeyValue, ModifiedDate = GETDATE(), T.ChangeType, TableName = @TableName
FROM @TempDetails T
WHERE T.IsKeyColumn = 1
ORDER BY T.KeyValue  -- Optional, but adds consistancy

-- Insert change details
INSERT INTO @TableChangeLogDetails (SummaryID, ColumnName, OldValue, NewValue)
SELECT S.SummaryID, T.ColumnName, T.OldValue, T.NewValue
FROM @CaptureSummaryID S
JOIN @TempDetails T ON T.KeyValue = S.KeyValue
WHERE T.IsKeyColumn = 0
ORDER BY T.ColumnName  -- Optional, but adds consistancy

-- View test results
SELECT 'Change Log:', *
FROM @TableChangeLogSummary S
LEFT JOIN @TableChangeLogDetails D ON D.SummaryID = S.ID
ORDER BY S.ID, D.ID