Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Stored Procedure performance issues related to SQL when querying the Sitecore Reference Data database - Sitecore.Support.312397
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
@GlennvG

This comment has been minimized.

Copy link

GlennvG commented Sep 5, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.