Skip to content

Instantly share code, notes, and snippets.

@martinrayenglish
Created August 17, 2019 13:23
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save martinrayenglish/aba32e800618784a674eae5a3b12c5f2 to your computer and use it in GitHub Desktop.
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
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