1

I work with large third party data-sets. Long experience has taught me that it is a really good idea to give incoming lines of data a surrogate ID as soon as it enters my system so I can track it easily as it's validated, warehoused, etc. The problem is the key values can potentially be every dimensional value, which could be 200 columns.

My general process is this:

  1. load data to a staging table.
  2. match the data with a second IdMatch table that holds only the key values and surrogate IDS.

    IF OBJECT_ID('Staging.myTest') IS NULL
        CREATE TABLE Staging.myTest (
            [ID] INT IDENTITY(1,1) NOT NULL,
            [Hash] INT NULL,
            [Dim_1] NVARCHAR(32) NOT NULL,
            [Dim_2] NVARCHAR(32) NOT NULL,
            [Dim_3] NVARCHAR(32) NULL,
            [Met_1] INT NULL,
            [Met_2] DECIMAL(5,2) NULL
        );
    
    IF OBJECT_ID('IdMatch.myTest') IS NULL
        CREATE TABLE IdMatch.myTest (
            [ID] INT IDENTITY(1,1) NOT NULL,
            [Hash] INT NULL,
            [Dim_1] NVARCHAR(32) NOT NULL,
            [Dim_2] NVARCHAR(32) NOT NULL,
            [Dim_3] NVARCHAR(32) NULL,
        );
    
    TRUNCATE TABLE Staging.myTest;
    TRUNCATE TABLE IdMatch.myTest;
    
    INSERT INTO Staging.myTest
        ([Dim_1], [Dim_2], [Dim_3])
    VALUES ('A', 'A', 'A'),
        ('B', 'B', 'B'),
        ('C', 'C', NULL),
        ('C', 'C', 'C'),
        ('D', 'D', 'D');
    
    INSERT INTO IdMatch.myTest
        ([Dim_1], [Dim_2], [Dim_3])
    VALUES ('A', 'A', 'A');
    
    --My Proc (as script) for setting the index.
    INSERT INTO [IdMatch].myTest
        ([Dim_1], [Dim_2], [Dim_3])
    SELECT src.[Dim_1], src.[Dim_2], src.[Dim_3]
    FROM Staging.myTest AS src
    WHERE NOT EXISTS (
            SELECT tgt.[Dim_1], tgt.[Dim_2], tgt.[Dim_3]
            FROM [IdMatch].myTest AS tgt
            WHERE tgt.[Dim_1] = src.[Dim_1]
                AND tgt.[Dim_2] = src.[Dim_2]
                AND tgt.[Dim_3] = src.[Dim_3]
        );
    
    SELECT * FROM IdMatch.myTest
    

THE PROBLEM: Matching to get a surrogate ID this way takes a long time when the real data-set contains 200+ columns of NVARCHAR data. Is there a better way? I've tried pre-computing hashes, but am not sure how to handle the eventual collisions I'll produce.

2 Answers 2

1

One common approach is to choose a hash function with a very, very small chance of collision and then assume there won't be any:

CREATE TABLE Staging.myTest (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Hash] AS 
        CONVERT(binary(32), 
            HASHBYTES('SHA2_256', 
                CONCAT(Dim_1, N'|', Dim_2, N'|', Dim_3))),
    [Dim_1] NVARCHAR(32) NOT NULL,
    [Dim_2] NVARCHAR(32) NOT NULL,
    [Dim_3] NVARCHAR(32) NULL,
    [Met_1] INT NULL,
    [Met_2] DECIMAL(5,2) NULL
);
GO
CREATE TABLE IdMatch.myTest (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Hash] AS 
        CONVERT(binary(32), 
            HASHBYTES('SHA2_256', 
                CONCAT(Dim_1, N'|', Dim_2, N'|', Dim_3))),
    [Dim_1] NVARCHAR(32) NOT NULL,
    [Dim_2] NVARCHAR(32) NOT NULL,
    [Dim_3] NVARCHAR(32) NULL,
);
GO
-- Declared unique because we have decided it will be
CREATE UNIQUE NONCLUSTERED INDEX 
    IX_HASH 
ON IdMatch.myTest 
    ([Hash]);

Note: Most people replace column NULLs with empty string for hashing, which is the default behaviour of CONCAT. If you need to differentiate NULL from empty string, you would need to identify some other magic value to use, and wrap the nullable column with ISNULL or COALESCE.

Adding rows that don't match is then:

INSERT Staging.myTest
(
    Dim_1, 
    Dim_2, 
    Dim_3
)
SELECT
    SRC.Dim_1,
    SRC.Dim_2,
    SRC.Dim_3
FROM Staging.myTest AS SRC
WHERE
    NOT EXISTS
    (
        SELECT 1
        FROM IdMatch.myTest AS TGT
        WHERE
            TGT.[Hash] = SRC.[Hash]
    );

See Finding rows that have changed in T-SQL – CHECKSUM, BINARY_CHECKSUM, HASHBYTES by Greg Low. You should test this approach with your data to see if this scheme will work for you.

0
1

Your previous question mentioned using CHECKSUM as a hash. This is generally not workable as mentioned in my other answer, but if you would like to try it, a possible implementation is below:

CREATE TABLE Staging.myTest (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Hash] AS CHECKSUM(Dim_1, Dim_2, Dim_3),
    [Dim_1] NVARCHAR(32) NOT NULL,
    [Dim_2] NVARCHAR(32) NOT NULL,
    [Dim_3] NVARCHAR(32) NULL,
    [Met_1] INT NULL,
    [Met_2] DECIMAL(5,2) NULL
);
GO
CREATE TABLE IdMatch.myTest (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Hash] AS CHECKSUM(Dim_1, Dim_2, Dim_3),
    [Dim_1] NVARCHAR(32) NOT NULL,
    [Dim_2] NVARCHAR(32) NOT NULL,
    [Dim_3] NVARCHAR(32) NULL,
);
GO
-- For lookups
CREATE UNIQUE CLUSTERED INDEX c ON IdMatch.myTest (ID);
GO
-- Not unique!
CREATE NONCLUSTERED INDEX 
    IX_HASH 
ON IdMatch.myTest 
    ([Hash]);

To find new rows, we also need to account for hash collisions. The idea here is to only check columns in detail once the hash check has found a potential match:

INSERT Staging.myTest 
(
    Dim_1, 
    Dim_2, 
    Dim_3
)
SELECT
    SRC.Dim_1,
    SRC.Dim_2,
    SRC.Dim_3
FROM Staging.myTest AS SRC
LEFT JOIN IdMatch.myTest AS HSH
    ON HSH.[Hash] = SRC.[Hash]
WHERE
    1 = CASE
            -- No hash match, definitely missing (pass through)
            WHEN HSH.[Hash] IS NULL THEN 1
            -- Hash match, check columns in detail to confirm
            WHEN NOT EXISTS
            (
                -- Null-aware column comparison
                SELECT
                    -- Source column list
                    SRC.Dim_1, SRC.Dim_2, SRC.Dim_3
                INTERSECT 
                SELECT
                    -- Target column list
                    T.Dim_1, T.Dim_2, T.Dim_3
                FROM IdMatch.myTest AS T
                WHERE
                    T.ID = HSH.ID
            ) THEN 1
            -- Otherwise exact match already exists
            ELSE 0
        END;

The null-aware column comparison is explained in my article, Undocumented Query Plans: Equality Comparisons.

There are a few ways to write this query correctly, and guarantee a hash check before a full column comparison. I wrote this one using CASE because it makes the logic quite explicit, and I like the execution plan shape.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.