Stored Procedure performance issues related to SQL when querying the Sitecore Reference Data database - Sitecore.Support.312397

Sitecore.Support.312397.sql

ALTER PROCEDURE [xdb_refdata].[DeleteDefinitions]
(
    @Keys [xdb_refdata].[DefinitionKeys] READONLY
)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @Results [xdb_refdata].[DefinitionResults]

    DECLARE @DefinitionsToProcess TABLE
    (
        [DefinitionInstanceID] UNIQUEIDENTIFIER,
        [ID] UNIQUEIDENTIFIER,
        [Moniker] NVARCHAR(MAX),
        [TypeID] UNIQUEIDENTIFIER,
        [Version] SMALLINT
    )

    INSERT INTO
        @DefinitionsToProcess
    (
        [DefinitionInstanceID],
        [ID],
        [Moniker],
        [TypeID],
        [Version]
    )
    SELECT
        NEWID(),
        -- Performance optimization: do not use [xdb_refdata].[GetDefinitionID] and [xdb_refdata].[GetDefinitionMonikerHash] functions. Instead, put their code in.
        -- https://www.sqlshack.com/improvements-of-scalar-user-defined-function-performance-in-sql-server-2019/
        -- Bug #312397
        (
            SELECT TOP 1
                [xdb_refdata].[DefinitionMonikers].[ID]
            FROM
                [xdb_refdata].[DefinitionMonikers]
            INNER JOIN
                [xdb_refdata].[Definitions]
            ON
                [xdb_refdata].[Definitions].[ID] = [xdb_refdata].[DefinitionMonikers].[ID]
            WHERE
                [xdb_refdata].[DefinitionMonikers].[MonikerHash] = HASHBYTES('SHA2_256', [keys].[Moniker]) AND
                [xdb_refdata].[DefinitionMonikers].[Moniker] = [keys].[Moniker] AND
                [xdb_refdata].[Definitions].[TypeID] = [keys].[TypeID]
        ),
        [Moniker],
        [TypeID],
        [Version]
    FROM
        @Keys [keys]

    -- Filter definitions which cannot be found
    INSERT INTO
        @Results
    (
        [DefinitionInstanceID],
        [Moniker],
        [TypeID],
        [Version],
        [Success],
        [Message]
    )
    SELECT
        [DefinitionInstanceID],
        [Moniker],
        [TypeID],
        [Version],
        0,
        'The definition was not found.'
    FROM
        @DefinitionsToProcess
    WHERE
        [ID] IS NULL

    DELETE
        @DefinitionsToProcess
    WHERE
        [ID] IS NULL

    -- Filter definitions where version doesn't exist
    INSERT INTO
        @Results
    (
        [DefinitionInstanceID],
        [Moniker],
        [TypeID],
        [Version],
        [Success],
        [Message]
    )
    SELECT
        [ToProcess].[DefinitionInstanceID],
        [ToProcess].[Moniker],
        [ToProcess].[TypeID],
        [ToProcess].[Version],
        0,
        'The definition version was not found.'
    FROM
        @DefinitionsToProcess AS [ToProcess]
    WHERE
        NOT EXISTS (
            SELECT
                [ID],
                [Version]
            FROM
                [xdb_refdata].[Definitions]
            WHERE
                [xdb_refdata].[Definitions].[ID] = [ToProcess].[ID] AND
                [xdb_refdata].[Definitions].[Version] = [ToProcess].[Version]
        )

    DELETE
        [Target]
    FROM
        @DefinitionsToProcess AS [Target]
    WHERE
        NOT EXISTS (
            SELECT
                [ID],
                [Version]
            FROM
                [xdb_refdata].[Definitions]
            WHERE
                [xdb_refdata].[Definitions].[ID] = [Target].[ID] AND
                [xdb_refdata].[Definitions].[Version] = [Target].[Version]
        )

    -- Filter definitions which are still active
    INSERT INTO
        @Results
    (
        [DefinitionInstanceID],
        [Moniker],
        [TypeID],
        [Version],
        [Success],
        [Message]
    )
    SELECT
        [ToProcess].[DefinitionInstanceID],
        [ToProcess].[Moniker],
        [ToProcess].[TypeID],
        [ToProcess].[Version],
        0,
        'The definition is still active.'
    FROM
        @DefinitionsToProcess AS [ToProcess]
    INNER JOIN
        [xdb_refdata].[Definitions]
    ON
        [xdb_refdata].[Definitions].[ID] = [ToProcess].[ID] AND
        [xdb_refdata].[Definitions].[Version] = [ToProcess].[Version]
    WHERE
        [IsActive] = 1

    DELETE
        [Target]
    FROM
        @DefinitionsToProcess AS [Target]
    INNER JOIN
        [xdb_refdata].[Definitions]
    ON
        [xdb_refdata].[Definitions].[ID] = [Target].[ID] AND
        [xdb_refdata].[Definitions].[Version] = [Target].[Version]
    WHERE
        [IsActive] = 1

    -- Delete culture data for definitions
    DELETE
        [Target]
    FROM
        [DefinitionCultures] AS [Target]
    INNER JOIN
        @DefinitionsToProcess AS [ToProcess]
    ON
        [Target].[ID] = [ToProcess].[ID] AND
        [Target].[Version] = [ToProcess].[Version]

    -- Delete definitions
    DELETE
        [Target]
    OUTPUT
        [ToProcess].[DefinitionInstanceID],
        [ToProcess].[Moniker],
        [ToProcess].[TypeID],
        [ToProcess].[Version],
        1,
        NULL
    INTO
        @Results
    FROM
        [xdb_refdata].[Definitions] AS [Target]
    INNER JOIN
        @DefinitionsToProcess AS [ToProcess]
    ON
        [Target].[ID] = [ToProcess].[ID] AND
        [Target].[Version] = [ToProcess].[Version]

    -- Delete monikers which are no longer being used
    DELETE
        [Target]
    FROM
        [xdb_refdata].[DefinitionMonikers] AS [Target]
    INNER JOIN
        @DefinitionsToProcess AS [ToProcess]
    ON
        [Target].[ID] = [ToProcess].[ID]
    WHERE (
        SELECT
            COUNT([ID])
        FROM
            [xdb_refdata].[Definitions]
        WHERE
            [xdb_refdata].[Definitions].[ID] = [ToProcess].[ID]
    ) = 0

    -- return results   
    SELECT 
        [DefinitionInstanceID],
        [Moniker],
        [TypeID],
        [Version],
        [Success],
        [Message] 
    FROM 
        @Results;
END

GO

ALTER PROCEDURE [xdb_refdata].[GetDefinitions]
(
    @Criteria [xdb_refdata].[DefinitionCriteria] READONLY,
    @LatestActiveOnly BIT,
    -- Used for selecting only one of the result sets. 1 = Definitions, 2 = DefinitionCultures, anything else = both
    @ResultSetSelector SMALLINT = 0
)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    IF @LatestActiveOnly IS NULL
    BEGIN
        RAISERROR('Parameter @LatestActiveOnly is NULL', 16, 1) WITH NOWAIT;
        RETURN
    END

    DECLARE
        @MatchingDefinitions [xdb_refdata].[DefinitionBatch]

    DECLARE @MappedCriteria TABLE
    (
        [ID] UNIQUEIDENTIFIER,
        [Version] SMALLINT,
        [Culture] VARCHAR(84)
    )

    -- Resolve IDs for monikers
    INSERT INTO @MappedCriteria
    SELECT
        -- Performance optimization: do not use [xdb_refdata].[GetDefinitionID] and [xdb_refdata].[GetDefinitionMonikerHash] functions. Instead, put their code in.
        -- https://www.sqlshack.com/improvements-of-scalar-user-defined-function-performance-in-sql-server-2019/
        -- Bug #312397
        (
            SELECT TOP 1
                [xdb_refdata].[DefinitionMonikers].[ID]
            FROM
                [xdb_refdata].[DefinitionMonikers]
            INNER JOIN
                [xdb_refdata].[Definitions]
            ON
                [xdb_refdata].[Definitions].[ID] = [xdb_refdata].[DefinitionMonikers].[ID]
            WHERE
                [xdb_refdata].[DefinitionMonikers].[MonikerHash] = HASHBYTES('SHA2_256', [criteria].[Moniker]) AND
                [xdb_refdata].[DefinitionMonikers].[Moniker] = [criteria].[Moniker] AND
                [xdb_refdata].[Definitions].[TypeID] = [criteria].[TypeID]
        ),
        [Version],
        [Culture]
    FROM
        @Criteria [criteria]

    -- Populate latest versions where no specific version was supplied
    IF @LatestActiveOnly = 1
    BEGIN
        UPDATE @MappedCriteria
        SET
            [Version] = MaxVersion
            FROM
            (
                SELECT
                    MAX([Version]) AS MaxVersion,
                    [ID]
                FROM
                    [xdb_refdata].[Definitions]
                WHERE
                    [Definitions].[ID] = [ID] AND
                    [Definitions].[IsActive] = 1
                GROUP BY
                    [ID]
            ) MaxVersions
            INNER JOIN
                @MappedCriteria AS [MappedCriteria]
            ON
                [MappedCriteria].[ID] = MaxVersions.[ID]
        WHERE [Version] IS NULL
    END
    ELSE
    BEGIN
        INSERT INTO @MappedCriteria
        (
            [ID],
            [Version],
            [Culture]
        )
            SELECT DISTINCT
                [MappedCriteria].[ID],
                [xdb_refdata].[Definitions].[Version],
                [MappedCriteria].[Culture]
            FROM
                [xdb_refdata].[Definitions]
            INNER JOIN 
                @MappedCriteria AS [MappedCriteria]
            ON
                [xdb_refdata].[Definitions].ID = [MappedCriteria].ID
            WHERE
                [MappedCriteria].[Version] IS NULL

        DELETE @MappedCriteria
        WHERE
            [Version] IS NULL
    END

    -- Find matching definitions
    INSERT INTO
        @MatchingDefinitions
    (
        [ID],
        [TypeID],
        [Version],
        [IsActive],
        [LastModified],
        [DataTypeRevision],
        [Data]
    )
    SELECT DISTINCT
        [xdb_refdata].[Definitions].[ID],
        [xdb_refdata].[Definitions].[TypeID],
        [xdb_refdata].[Definitions].[Version],
        [xdb_refdata].[Definitions].[IsActive],
        [xdb_refdata].[Definitions].[LastModified],
        [xdb_refdata].[Definitions].[DataTypeRevision],
        [xdb_refdata].[Definitions].[Data]
    FROM
        [xdb_refdata].[Definitions]
    INNER JOIN
        @MappedCriteria [Criteria]
    ON
        [Criteria].[ID] = [xdb_refdata].[Definitions].[ID] AND
        [Criteria].[Version] = [xdb_refdata].[Definitions].[Version]

    IF @ResultSetSelector IS NULL OR @ResultSetSelector != 2
    BEGIN
        SELECT
            [xdb_refdata].[GetDefinitionMoniker]([MatchingDefinitions].[ID]) AS [Moniker],
            [TypeID],
            [Version],
            [IsActive],
            [LastModified],
            [DataTypeRevision],
            [Data]
        FROM
            @MatchingDefinitions AS [MatchingDefinitions]
        ORDER BY
            [Moniker]
    END

    IF @ResultSetSelector IS NULL OR @ResultSetSelector != 1
    BEGIN
        -- Find cultures for definitions
        SELECT
            [xdb_refdata].[GetDefinitionMoniker]([MatchingDefinitions].[ID]) AS [Moniker],
            [MatchingDefinitions].[TypeID],
            [MatchingDefinitions].[Version],
            [xdb_refdata].[DefinitionCultures].[Culture],
            [xdb_refdata].[DefinitionCultures].[Data]
        FROM
            @MatchingDefinitions AS [MatchingDefinitions]
        INNER JOIN
            [xdb_refdata].[DefinitionCultures]
        ON
            [xdb_refdata].[DefinitionCultures].[ID] = [MatchingDefinitions].[ID] AND
            [xdb_refdata].[DefinitionCultures].[Version] = [MatchingDefinitions].[Version]
        INNER JOIN
            @MappedCriteria [Criteria]
        ON
            [Criteria].[ID] = [xdb_refdata].[DefinitionCultures].[ID] AND
            [Criteria].[Version] = [xdb_refdata].[DefinitionCultures].[Version]
        WHERE
            [Criteria].[Culture] IS NULL OR
            [Criteria].[Culture] = [xdb_refdata].[DefinitionCultures].[Culture]
    END
END

GO

ALTER PROCEDURE [xdb_refdata].[SaveDefinitionCultures]
(
    @DefinitionCultures [xdb_refdata].[DefinitionCultureBatch] READONLY
)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @Results [xdb_refdata].[DefinitionCultureResults],
        @DefinitionInstanceID UNIQUEIDENTIFIER,
        @Moniker NVARCHAR(MAX),
        @TypeID UNIQUEIDENTIFIER,
        @Version SMALLINT,
        @Culture VARCHAR(84),
        @Data VARBINARY(MAX),
        @IsUpdate BIT,
        @Hash BINARY(32)

    DECLARE InputDefinitionCulture CURSOR FOR
        SELECT [DefinitionInstanceID], [Moniker], [TypeID], [Version], [Culture], [Data] FROM @DefinitionCultures

    OPEN InputDefinitionCulture

    FETCH NEXT FROM InputDefinitionCulture
        INTO @DefinitionInstanceID, @Moniker, @TypeID, @Version, @Culture, @Data

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            SET @IsUpdate = 0

            -- Performance optimization: do not use [xdb_refdata].[GetDefinitionID] and [xdb_refdata].[GetDefinitionMonikerHash] functions. Instead, put their code in.
            -- https://www.sqlshack.com/improvements-of-scalar-user-defined-function-performance-in-sql-server-2019/
            -- Bug #312397
            SET @Hash = HASHBYTES('SHA2_256', @Moniker)
            DECLARE @ID UNIQUEIDENTIFIER = (
                SELECT TOP 1
                    [xdb_refdata].[DefinitionMonikers].[ID]
                FROM
                    [xdb_refdata].[DefinitionMonikers]
                INNER JOIN
                    [xdb_refdata].[Definitions]
                ON
                    [xdb_refdata].[Definitions].[ID] = [xdb_refdata].[DefinitionMonikers].[ID]
                WHERE
                    [xdb_refdata].[DefinitionMonikers].[MonikerHash] = @Hash AND
                    [xdb_refdata].[DefinitionMonikers].[Moniker] = @Moniker AND
                    [xdb_refdata].[Definitions].[TypeID] = @TypeID
            )

            IF @ID IS NULL
            BEGIN
                INSERT INTO @Results
                (
                    [DefinitionInstanceID],
                    [Moniker],
                    [TypeID],
                    [Version],
                    [Culture],
                    [Success],
                    [Message]
                )
                VALUES
                (
                    @DefinitionInstanceID,
                    @Moniker,
                    @TypeID,
                    @Version,
                    @Culture,
                    0,
                    'The definition was not found'
                )
            END
            ELSE
            BEGIN
                IF EXISTS ( SELECT 1 FROM [xdb_refdata].[DefinitionCultures] WITH (UPDLOCK) WHERE [ID] = @ID AND [Version] = @Version AND [Culture] = @Culture)
                BEGIN
                    UPDATE [xdb_refdata].[DefinitionCultures] WITH (UPDLOCK) SET
                        [Data] = @Data
                    WHERE
                        [ID] = @ID AND
                        [Version] = @Version AND
                        [Culture] = @Culture

                    SET @IsUpdate = 1
                END
                ELSE
                BEGIN
                    INSERT INTO [xdb_refdata].[DefinitionCultures] WITH (UPDLOCK)
                    (
                        [ID],
                        [Version],
                        [Culture],
                        [Data]
                    )
                    VALUES
                    (
                        @ID,
                        @Version,
                        @Culture,
                        @Data
                    )
                END

                INSERT INTO @Results
                (
                    [DefinitionInstanceID],
                    [Moniker],
                    [TypeID],
                    [Version],
                    [Culture],
                    [Success],
                    [Message]
                )
                VALUES
                (
                    @DefinitionInstanceID,
                    @Moniker,
                    @TypeID,
                    @Version,
                    @Culture,
                    1,
                    CASE
                        WHEN @IsUpdate = 1
                            THEN 'UPDATE'
                        ELSE
                            NULL
                    END
                )
            END
        END TRY
        BEGIN CATCH
            INSERT INTO @Results
            (
                [DefinitionInstanceID],
                [Moniker],
                [TypeID],
                [Version],
                [Culture],
                [Success],
                [Message]
            )
            VALUES
            (
                @DefinitionInstanceID,
                @Moniker,
                @TypeID,
                @Version,
                @Culture,
                0,
                ERROR_MESSAGE()
            )
        END CATCH

        FETCH NEXT FROM InputDefinitionCulture
            INTO @DefinitionInstanceID, @Moniker, @TypeID, @Version, @Culture, @Data
    END

    CLOSE InputDefinitionCulture
    DEALLOCATE InputDefinitionCulture

    SELECT
        [DefinitionInstanceID],
        [Moniker],
        [TypeID],
        [Version],
        [Culture],
        [Success],
        [Message]
    FROM @Results
    AS Results
END

GO

ALTER PROCEDURE [xdb_refdata].[SaveDefinitions]
(
    @Definitions [xdb_refdata].[MonikerDefinitions] READONLY
)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE
        @Results [xdb_refdata].[DefinitionResults],
        @CurrentDate DATETIME2(0) = CONVERT(DATETIME2(0), GETUTCDATE()),
        @DefinitionInstanceID UNIQUEIDENTIFIER,
        @Moniker NVARCHAR(MAX),
        @TypeID UNIQUEIDENTIFIER,
        @ID UNIQUEIDENTIFIER,
        @Version SMALLINT,
        @IsActive BIT,
        @DataTypeRevision SMALLINT,
        @Data VARBINARY(MAX),
        @IsUpdate BIT,
        @Hash BINARY(32)

    DECLARE InputDefinition CURSOR FOR
        SELECT [DefinitionInstanceID], [Moniker], [TypeID], [Version], [IsActive], [DataTypeRevision], [Data] FROM @Definitions

    OPEN InputDefinition

    FETCH NEXT FROM InputDefinition
        INTO @DefinitionInstanceID, @Moniker, @TypeID, @Version, @IsActive, @DataTypeRevision, @Data

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            SET @IsUpdate = 1

            -- Performance optimization: do not use [xdb_refdata].[GetDefinitionID] and [xdb_refdata].[GetDefinitionMonikerHash] functions. Instead, put their code in.
            -- https://www.sqlshack.com/improvements-of-scalar-user-defined-function-performance-in-sql-server-2019/
            -- Bug #312397
            SET @Hash = HASHBYTES('SHA2_256', @Moniker)
            SET @ID = (
                SELECT TOP 1
                    [xdb_refdata].[DefinitionMonikers].[ID]
                FROM
                    [xdb_refdata].[DefinitionMonikers]
                INNER JOIN
                    [xdb_refdata].[Definitions]
                ON
                    [xdb_refdata].[Definitions].[ID] = [xdb_refdata].[DefinitionMonikers].[ID]
                WHERE
                    [xdb_refdata].[DefinitionMonikers].[MonikerHash] = @Hash AND
                    [xdb_refdata].[DefinitionMonikers].[Moniker] = @Moniker AND
                    [xdb_refdata].[Definitions].[TypeID] = @TypeID
            )

            IF(@ID IS NULL)
            BEGIN

                BEGIN TRANSACTION [Definition]

                -- Performance optimization: do not use [xdb_refdata].[GetDefinitionID] and [xdb_refdata].[GetDefinitionMonikerHash] functions. Instead, put their code in.
                -- https://www.sqlshack.com/improvements-of-scalar-user-defined-function-performance-in-sql-server-2019/
                -- Bug #312397
                SET @ID = (
                    SELECT TOP 1
                        [xdb_refdata].[DefinitionMonikers].[ID]
                    FROM
                        [xdb_refdata].[DefinitionMonikers]
                    INNER JOIN
                        [xdb_refdata].[Definitions]
                    ON
                        [xdb_refdata].[Definitions].[ID] = [xdb_refdata].[DefinitionMonikers].[ID]
                    WHERE
                        [xdb_refdata].[DefinitionMonikers].[MonikerHash] = @Hash AND
                        [xdb_refdata].[DefinitionMonikers].[Moniker] = @Moniker AND
                        [xdb_refdata].[Definitions].[TypeID] = @TypeID
                )

                IF(@ID IS NULL)
                BEGIN
                    SET @IsUpdate = 0
                    SET @ID = NEWID()

                    INSERT INTO
                        [xdb_refdata].[DefinitionMonikers]
                    (
                        [ID],
                        [Moniker],
                        [MonikerHash]
                    )
                    VALUES
                    (
                        @ID,
                        @Moniker,
                        @Hash
                    )

                    INSERT INTO [xdb_refdata].[Definitions]
                    (
                        [ID],
                        [Version],
                        [TypeID],
                        [IsActive],
                        [LastModified],
                        [DataTypeRevision],
                        [Data]
                    )
                    VALUES
                    (
                        @ID,
                        @Version,
                        @TypeID,
                        @IsActive,
                        @CurrentDate,
                        @DataTypeRevision,
                        @Data
                    )
                END

                COMMIT TRANSACTION [Definition]

            END

            IF EXISTS ( SELECT 1 FROM [xdb_refdata].[Definitions] WHERE [ID] = @ID AND [Version] = @Version )
            BEGIN
                UPDATE [xdb_refdata].[Definitions]
                SET
                    [TypeID] = @TypeID,
                    [IsActive] = @IsActive,
                    [LastModified] = @CurrentDate,
                    [DataTypeRevision] = @DataTypeRevision,
                    [Data] = @Data
                WHERE
                    [ID] = @ID AND
                    [Version] = @Version
            END
            ELSE
            BEGIN
                SET @IsUpdate = 0

                INSERT INTO [xdb_refdata].[Definitions]
                (
                    [ID],
                    [Version],
                    [TypeID],
                    [IsActive],
                    [LastModified],
                    [DataTypeRevision],
                    [Data]
                )
                VALUES
                (
                    @ID,
                    @Version,
                    @TypeID,
                    @IsActive,
                    @CurrentDate,
                    @DataTypeRevision,
                    @Data
                )
            END

            INSERT INTO @Results
            (
                [DefinitionInstanceID],
                [Moniker],
                [TypeID],
                [Version],
                [Success],
                [Message]
            )
            VALUES
            (
                @DefinitionInstanceID,
                @Moniker,
                @TypeID,
                @Version,
                1,
                CASE
                    WHEN @IsUpdate = 1
                        THEN 'UPDATE'
                    ELSE
                        NULL
                END
            )
        END TRY
        BEGIN CATCH
            IF(@@TRANCOUNT > 0)
            BEGIN
                ROLLBACK TRANSACTION [Definition]
            END

            INSERT INTO @Results
            (
                [DefinitionInstanceID],
                [Moniker],
                [TypeID],
                [Version],
                [Success],
                [Message]
            )
            VALUES
            (
                @DefinitionInstanceID,
                @Moniker,
                @TypeID,
                @Version,
                0,
                ERROR_MESSAGE()
            )
        END CATCH

        FETCH NEXT FROM InputDefinition
        INTO @DefinitionInstanceID, @Moniker, @TypeID, @Version, @IsActive, @DataTypeRevision, @Data
    END

    CLOSE InputDefinition
    DEALLOCATE InputDefinition

    SELECT
        [DefinitionInstanceID],
        [Moniker],
        [TypeID],
        [Version],
        [Success],
        [Message]
    FROM @Results 
    AS Results
END

For what version of Sitecore did you use this? I'm having issues that the refdata database is 100% all the time.