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