Sitecore – Speeding up the upgrade process for the Reference Data Database

When you’re upgrading from Sitecore 9.2 to any higher versions, then there have been major changes in the setup of the Reference Data database. Within 9.2 and lower, there was a seperate table for the DefinitionMonikers, while in higher versions this table is merged with the Definintions itself.

Depending on the amount of records within your Definitions table, the normal upgrade script (SXP_referencedata_Part1.sql) can take several hours. For each record in Definitions, the script does the following:

  • Check if the record exists in the new Definitions table, if so, generate a new id
  • Insert the record in the new Definitions table
  • Select all the matching rows in the DefinitionCultures table and insert them into the new DefinitionCultures table
  • Delete the rows inserted in the new DefinitionCultures table from the old DefinitionCultures table
  • Delete the Definition from the old Definition table.

This works, but isn’t really efficient, as a few Definition types will never have records in the DefinitionCultures table. This means that we can skip all actions after “Insert the record in the new definitions table”, which will save a lot of time.

The following Definition Types do not have Definition Cultures attached:

  • Tracking Dictionary – GeoIpData
  • Tracking Dictionary – DeviceData
  • Tracking Dictionary – LocationData
  • Tracking Dictionary – UserAgentData
  • sitecore.exa.segment

The speed improvement

So now we know that there are some Definition Types that do not have Definition Cultures attached, we can easily improve the script by getting all the Definition Types that do not have Definition Cultures attached, and skipping those in the extra queries.

To get the Definition Types that have a culture attached, we can use the following query:

   SELECT 
            [xdb_refdata].[DefinitionTypes].[ID]
        FROM
            [xdb_refdata].[DefinitionTypes] 
        WHERE
            [xdb_refdata].[DefinitionTypes].[ID] IN 
            (
                SELECT 
                    distinct([xdb_refdata].[Definitions].[TypeID])
                FROM 
                    [xdb_refdata].[DefinitionCultures]
                inner join 
                    [xdb_refdata].[Definitions] on [xdb_refdata].[Definitions].[ID] = [xdb_refdata].[DefinitionCultures].[ID]
            )

We can insert the data that we retrieve in the above query, store it in a temporary table, and query it to check if we need to do the additional queries. An improved upgrade script for Sitecore 10.2 can be found here. This took the upgrade time from 2 hours to 5 minutes on my machine.

The full change is as follows:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xdb_refdata].[DefinitionMonikers]') AND type in (N'U'))
BEGIN
    PRINT 'Migrating definition types...'

    INSERT INTO [xdb_refdata].[tmp_DefinitionTypes]([ID], [Name])
        SELECT [ID], [Name]
        FROM [xdb_refdata].[DefinitionTypes]
        WHERE [ID] NOT IN (SELECT [ID] FROM [xdb_refdata].[tmp_DefinitionTypes]);

    PRINT CAST(ROWCOUNT_BIG() AS NVARCHAR) + ' have been migrated.'

    PRINT 'Migrating definitions...'

    DECLARE
        @OldID UNIQUEIDENTIFIER,
        @NewID UNIQUEIDENTIFIER,
        @Version SMALLINT,
        @Moniker NVARCHAR(300),
        @TypeID UNIQUEIDENTIFIER,
        @IsActive BIT,
        @LastModified DATETIME2(0),
        @DataTypeRevision SMALLINT,
        @Data VARBINARY(MAX),
        @MigratedDefinitionsCount BIGINT = 0

    DECLARE @OldDefinitions TABLE
    (
        [ID] UNIQUEIDENTIFIER,
        [Version] SMALLINT,
        [TypeID] UNIQUEIDENTIFIER,
        [Moniker] NVARCHAR(MAX),
        [IsActive] BIT,
        [LastModified] DATETIME2(0),
        [DataTypeRevision] SMALLINT,
        [Data] VARBINARY(MAX)
    )
    
    DECLARE @DefinitionTypesWithCulture TABLE
    (
        [ID] UNIQUEIDENTIFIER
    )
    
    INSERT INTO @DefinitionTypesWithCulture([ID])
        SELECT 
            [xdb_refdata].[DefinitionTypes].[ID]
        FROM
            [xdb_refdata].[DefinitionTypes] 
        WHERE
            [xdb_refdata].[DefinitionTypes].[ID] IN 
            (
                SELECT 
                    distinct([xdb_refdata].[Definitions].[TypeID])
                FROM 
                    [xdb_refdata].[DefinitionCultures]
                inner join 
                    [xdb_refdata].[Definitions] on [xdb_refdata].[Definitions].[ID] = [xdb_refdata].[DefinitionCultures].[ID]
            )

    INSERT INTO @OldDefinitions([ID], [Version], [TypeID], [Moniker], [IsActive], [LastModified], [DataTypeRevision], [Data])
        SELECT [defs].[ID], [Version], [TypeID], [Moniker], [IsActive], [LastModified], [DataTypeRevision], [Data] FROM [xdb_refdata].[Definitions] [defs]
        INNER JOIN [xdb_refdata].[DefinitionMonikers] [monikers] ON [monikers].[ID] = [defs].[ID]
        WHERE LEN([Moniker]) <= 300;

    DECLARE OldDefinitionsCursor CURSOR FAST_FORWARD FOR
        SELECT [ID], [Version], [TypeID], [Moniker], [IsActive], [LastModified], [DataTypeRevision], [Data] FROM @OldDefinitions

    OPEN OldDefinitionsCursor

    FETCH NEXT FROM OldDefinitionsCursor INTO @OldID, @Version, @TypeID, @Moniker, @IsActive, @LastModified, @DataTypeRevision, @Data

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @NewID = (SELECT TOP 1 [ID] FROM [xdb_refdata].[tmp_Definitions] WHERE [TypeID] = @TypeID AND [Moniker] = @Moniker AND [Version] = @Version);

        IF @NewID IS NULL
        BEGIN
            SET @NewID = NEWID();

            INSERT INTO [xdb_refdata].[tmp_Definitions]
                ([ID], [Version], [TypeID], [IsActive], [LastModified], [DataTypeRevision], [Data], [Moniker])
            VALUES
                (@NewID, @Version, @TypeID, @IsActive, @LastModified, @DataTypeRevision, @Data, @Moniker);
        END

        IF exists (select top(1)* from @DefinitionTypesWithCulture where [ID] = @TypeID)
        BEGIN

            DECLARE @S_NewId VARCHAR(36) = CONVERT(VARCHAR(36), @NewID)
            DECLARE @S_OldId VARCHAR(36) = CONVERT(VARCHAR(36), @OldID)

            DECLARE @insertIntoTmpDefenitionCulture nvarchar(1000) =
            FORMATMESSAGE('
                DECLARE @G_NewId UNIQUEIDENTIFIER = ''%s'';
                INSERT INTO [xdb_refdata].[tmp_DefinitionCultures]
                ([ID], [DefinitionVersionID], [Culture], [Data])
                SELECT NEWID(), @G_NewId, [Culture], [Data]
                FROM [xdb_refdata].[DefinitionCultures]
                WHERE [ID] = ''%s'' AND [Version] = %i AND [Culture] NOT IN (
                    SELECT [Culture] FROM [xdb_refdata].[tmp_DefinitionCultures] WHERE [DefinitionVersionID] = @G_NewId);',
                @S_NewId, @S_OldId, @Version);

            EXEC(@insertIntoTmpDefenitionCulture);

            DECLARE @deleteFromDefenitionCulture varchar(400) =
            FORMATMESSAGE('
                DELETE FROM [xdb_refdata].[DefinitionCultures]
                WHERE [ID] = ''%s'' AND [Version] = %i',
                @S_OldID, @Version);

            EXEC(@deleteFromDefenitionCulture);
        END

      
        IF (ROWCOUNT_BIG() > 0)
        BEGIN
            SET @MigratedDefinitionsCount = @MigratedDefinitionsCount + 1
        END

        FETCH NEXT FROM OldDefinitionsCursor INTO @OldID, @Version, @TypeID, @Moniker, @IsActive, @LastModified, @DataTypeRevision, @Data
    END

    CLOSE OldDefinitionsCursor
    DEALLOCATE OldDefinitionsCursor

	DELETE defs FROM [xdb_refdata].[Definitions] [defs] INNER JOIN [xdb_refdata].[DefinitionMonikers] [monikers] ON [monikers].[ID] = [defs].[ID] WHERE LEN([Moniker]) <= 300;
    DELETE FROM [xdb_refdata].[DefinitionMonikers] WHERE [ID] NOT IN (SELECT [ID] FROM [xdb_refdata].[Definitions]);
    DELETE FROM [xdb_refdata].[DefinitionTypes] WHERE [ID] NOT IN (SELECT [TypeID] FROM [xdb_refdata].[Definitions]);

    PRINT CAST(@MigratedDefinitionsCount AS NVARCHAR) + ' definitions have been migrated.'
END
GO

Leave a Reply

Your email address will not be published.