Addressing Umbraco Performance Issues: A Critical Update for Version 13 and 14

DECLARE @var0 BIT = 1;
DECLARE @var1 BIT = 1;
DECLARE @var2 BIT = 0;
DECLARE @var3 BIT = 1;
DECLARE @var4 UNIQUEIDENTIFIER  = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972';
DECLARE @var5 BIT = 0;
DECLARE @var6 INT = 398650;

SELECT [umbracoNode].[id] AS [Id], [umbracoNode].[uniqueId] AS [Key], [umbracoNode].[level] AS [Level], [umbracoNode].[path] AS [Path], [umbracoNode].[sortOrder] AS [SortOrder], [umbracoNode].[parentId] AS [ParentId], [umbracoNode].[createDate] AS [CreateDate], [umbracoNode].[nodeUser] AS [CreatorId]
, [umbracoContent].[contentTypeId] AS [ContentTypeId]
, [umbracoDocument].[published] AS [Published], [umbracoDocument].[edited] AS [Edited]
, [umbracoContentVersion].[id] AS [VersionId], [umbracoContentVersion].[text] AS [EditName], [umbracoContentVersion].[versionDate] AS [EditVersionDate], [umbracoContentVersion].[userId] AS [EditWriterId]
, [umbracoDocumentVersion].[templateId] AS [EditTemplateId]
, [pcver].[id] AS [PublishedVersionId], [pcver].[text] AS [PubName], [pcver].[versionDate] AS [PubVersionDate], [pcver].[userId] AS [PubWriterId]
, [pdver].[templateId] AS [PubTemplateId]
, [nuEdit].[data] AS [EditData]
, [nuPub].[data] AS [PubData]
, [nuEdit].[dataRaw] AS [EditDataRaw]
, [nuPub].[dataRaw] AS [PubDataRaw]
FROM [umbracoNode]
INNER JOIN [umbracoNode] [x]
ON (([umbracoNode].[id] = [x].[id]) OR ([umbracoNode].[path] LIKE concat([x].[path],',%')))
INNER JOIN [umbracoContent]
ON ([umbracoNode].[id] = [umbracoContent].[nodeId])
INNER JOIN [umbracoDocument]
ON ([umbracoNode].[id] = [umbracoDocument].[nodeId])
INNER JOIN [umbracoContentVersion]
ON (([umbracoNode].[id] = [umbracoContentVersion].[nodeId]) AND [umbracoContentVersion].[current] = @var0)
INNER JOIN [umbracoDocumentVersion]
ON ([umbracoContentVersion].[id] = [umbracoDocumentVersion].[id])
LEFT JOIN [umbracoContentVersion] [pcver]
INNER JOIN [umbracoDocumentVersion] [pdver]
ON (([pcver].[id] = [pdver].[id]) AND ([pdver].[published] = @var1))
ON ([umbracoNode].[id] = [pcver].[nodeId])
LEFT JOIN [cmsContentNu] [nuEdit]
ON (([umbracoNode].[id] = [nuEdit].[nodeId]) AND [nuEdit].[published] = @var2)
LEFT JOIN [cmsContentNu] [nuPub]
ON (([umbracoNode].[id] = [nuPub].[nodeId]) AND ([nuPub].[published] = @var3))
WHERE ((([umbracoNode].[nodeObjectType] = @var4) AND ([umbracoNode].[trashed] = @var5)))
AND (([x].[id] = @var6))
ORDER BY [umbracoNode].[level], [umbracoNode].[parentId], [umbracoNode].[sortOrder]

-- This query returns 324324 records instead of 2
SELECT COUNT(*) FROM (
SELECT [umbracoNode].[id] AS [Id]
FROM [umbracoNode]
INNER JOIN [umbracoContent]
ON ([umbracoNode].[id] = [umbracoContent].[nodeId])
INNER JOIN [umbracoDocument]
ON ([umbracoNode].[id] = [umbracoDocument].[nodeId])
INNER JOIN [umbracoContentVersion]
ON (([umbracoNode].[id] = [umbracoContentVersion].[nodeId]) AND [umbracoContentVersion].[current] = 1)
INNER JOIN [umbracoDocumentVersion]
ON ([umbracoContentVersion].[id] = [umbracoDocumentVersion].[id])
LEFT JOIN [umbracoContentVersion] [pcver]
INNER JOIN [umbracoDocumentVersion] [pdver]
ON (([pcver].[id] = [pdver].[id]) AND [pdver].[published] = 1)
ON ([umbracoNode].[id] = [pcver].[nodeId])
WHERE ((([umbracoNode].[nodeObjectType] = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972') AND ([umbracoNode].[trashed] = 0)))
) npoco_tbl