Hi All,
I am working on below query which is performing slow. Based on the execution plan it seems that these two JOINS are taking huge amount of data. Can some please give expert advise which indexes should i create on this table.
Execution Plan: --> https://www.brentozar.com/pastetheplan/?id=rk4LZQl7U
JOINS
INNER JOIN dbo.ProductDataTemplate PDT WITH (NOLOCK)
ON PDT.fkProductId = P.ProductId
AND PDT.fkProductTypeId = 2
AND PDT.fkUniverseId = CPU.fkUniverseId
INNER JOIN dbo.ProductDataTemplate PDPT WITH (NOLOCK)
ON PDPT.fkProductId = P.ProductId
AND PDPT.isPartnerTemplate = 1
AND PDPT.fkUniverseId = CPU.fkUniverseId
Complete Query
DECLARE @BoxId INT = 1217707
DECLARE @PackagingSectionId INT = 1
DECLARE @LanguageId INT = 149
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @PhysicalProductFormatId INT = 1;
DECLARE @BoxCountryId INT = ( SELECT DISTINCT fkCountryId FROM dbo.ProductCountry WHERE fkProductId = @BoxId );
DECLARE @BoxUniverse INT = ( SELECT fkUniverseId FROM dbo.Product INNER JOIN dbo.ProductUniverse ON Product.ProductId = ProductUniverse.fkProductId WHERE Product.ProductId = @BoxId );
SELECT P.ShortName,
[ProductVersion].[VersionNumber] AS BoxVersion,
BXL.LanguageName AS BoxLanguage,
BXL.SBLanguageCode,
BU.UniverseName AS BoxUniverse,
P.ProductName AS BoxTitle,
B.BrandName AS BoxBrand,
C.CountryName AS BoxCountry,
PR.PartnerId,
PR.PartnerName,
PR.PartnerCode,
PDPT.fkUniverseId AS PartnerTemplateUniverseId,
EU.UniverseId,
EU.UniverseName,
CP.ProductId AS ExperienceId,
CP.ProductCode,
ExperienceCount = DENSE_RANK() OVER (PARTITION BY P.ProductId, PR.PartnerId ORDER BY ExperienceRank DESC, CP.ProductId ),
EPT.ProductTemplateId,
PPT.PartnerTemplateId,
--ISNULL([MinNumberOfPeople], '') as [MinNumberOfPeople],
--ISNULL([MaxNumberOfPeople], '') AS [MaxNumberOfPeople]
MinNumberOfPeople = ( SELECT TOP 1 FV.Value FROM dbo.ProductFacetValue PFV INNER JOIN dbo.FacetValue FV ON FV.FacetValueId = PFV.fkFacetValueId WHERE FV.fkFacetId = 45 AND PFV.fkProductId = CP.ProductId ),
MaxNumberOfPeople = ( SELECT TOP 1 FV.Value FROM dbo.ProductFacetValue PFV INNER JOIN dbo.FacetValue FV ON FV.FacetValueId = PFV.fkFacetValueId WHERE FV.fkFacetId = 44 AND PFV.fkProductId = CP.ProductId )
INTO #BoxExtract
FROM dbo.Product P
INNER JOIN dbo.RelatedProduct RP WITH (NOLOCK)
ON RP.fkProductId = P.ProductId
AND RP.Active = 1
INNER JOIN dbo.PartnerProduct PP WITH (NOLOCK)
ON PP.fkProductId = RP.fkProduct1Id
AND PP.Active = 1
INNER JOIN dbo.Partner PR WITH (NOLOCK)
ON PR.PartnerId = PP.fkPartnerId
AND PR.PartnerStatus = 1
INNER JOIN dbo.PartnerLocation PL WITH (NOLOCK)
ON PL.fkPartnerId = PR.PartnerId
INNER JOIN dbo.ProductUniverse PU WITH (NOLOCK)
ON PU.fkProductId = P.ProductId
LEFT JOIN dbo.MarketingRegionUniverse MRU WITH (NOLOCK)
ON MRU.UniverseId = P.fkUniverseBoxId
AND MRU.SubregionISOCode = PL.SubRegionISOCode
LEFT JOIN dbo.ProductBrand PB WITH (NOLOCK)
ON PB.fkProductId = P.ProductId
LEFT JOIN dbo.Brand B WITH (NOLOCK)
ON PB.fkBrandId = B.BrandId
LEFT JOIN dbo.Universe BU WITH (NOLOCK)
ON BU.UniverseId = P.fkUniverseBoxId
INNER JOIN dbo.Product CP WITH (NOLOCK)
ON CP.ProductId = RP.fkProduct1Id
AND CP.fkTriggerStateId = 4
-- LEFT JOIN
--(
-- SELECT PFV.fkProductId
-- ,MAX(CASE WHEN FV.fkfacetid = 45 THEN FV.[Value] ELSE '' END) AS [MinNumberOfPeople]
-- ,MAX(CASE WHEN FV.fkfacetid = 44 THEN FV.[Value] ELSE '' END) AS [MaxNumberOfPeople]
-- FROM ProductFacetValue PFV
-- INNER JOIN facetvalue FV
-- ON FV.FacetValueId = PFV.fkFacetValueId
-- WHERE FV.fkFacetId IN (44,45)
-- GROUP BY PFV.fkProductId
--) prod
--ON prod.fkProductId = CP.ProductId
INNER JOIN dbo.ProductUniverse CPU WITH (NOLOCK)
ON CPU.fkProductId = CP.ProductId
INNER JOIN dbo.Universe EU WITH (NOLOCK)
ON EU.UniverseId = CPU.fkUniverseId
INNER JOIN dbo.ProductLanguage BL WITH (NOLOCK)
ON BL.fkProductId = P.ProductId
LEFT JOIN dbo.Country PLC WITH (NOLOCK)
ON PLC.CountryName = PL.Country
LEFT JOIN dbo.CountryLocale CL WITH (NOLOCK)
ON CL.fkCountryId = PLC.CountryId
AND BL.fkLanguageId = CL.fkLanguageId
INNER JOIN dbo.Language BXL WITH (NOLOCK)
ON BXL.LanguageId = BL.fkLanguageId
LEFT JOIN dbo.ProductCountry PC WITH (NOLOCK)
ON PC.fkProductId = P.ProductId
LEFT JOIN dbo.Country C WITH (NOLOCK)
ON C.CountryId = PC.fkCountryId
INNER JOIN dbo.ProductDataTemplate PDT WITH (NOLOCK)
ON PDT.fkProductId = P.ProductId
AND PDT.fkProductTypeId = 2
AND PDT.fkUniverseId = CPU.fkUniverseId
INNER JOIN dbo.ProductDataTemplate PDPT WITH (NOLOCK)
ON PDPT.fkProductId = P.ProductId
AND PDPT.isPartnerTemplate = 1
AND PDPT.fkUniverseId = CPU.fkUniverseId
INNER JOIN dbo.ProductTemplate EPT WITH (NOLOCK)
ON EPT.fkProductId = CP.ProductId
AND EPT.fkLanguageId = BL.fkLanguageId
AND EPT.IsDeleted = 0
AND EPT.fkProductTemplateId IS NULL
AND EPT.fkTriggerStateId = 14
AND EPT.fkDataTemplateId = PDT.fkDataTemplateId
INNER JOIN dbo.PartnerTemplate PPT WITH (NOLOCK)
ON PPT.fkPartnerId = PR.PartnerId
AND PPT.fkLanguageId = BL.fkLanguageId
AND PPT.IsDeleted = 0
AND PPT.fkPartnerTemplateId IS NULL
AND PPT.fkTriggerStateId = 14
AND PPT.fkDataTemplateId = PDPT.fkDataTemplateId
LEFT JOIN
(
SELECT SUM(ps.Rank) AS ExperienceRank,
RPPS.fkRelatedProductId
FROM dbo.RelatedProductPackagingSection RPPS WITH (NOLOCK)
INNER JOIN dbo.PackagingSection ps WITH (NOLOCK)
ON ps.PackagingSectionId = RPPS.fkPackagingSectionId
GROUP BY RPPS.fkRelatedProductId
) AS ERank
ON RP.RelatedProductId = ERank.fkRelatedProductId
LEFT JOIN dbo.[ProductVersion] WITH (NOLOCK)
ON [ProductVersion].[fkProductId] = [P].[ProductId]
AND [ProductVersion].[ProductVersionId] =
(
SELECT MAX([ProductVersionId])
FROM dbo.[ProductVersion] [prv]
WHERE [prv].[fkProductId] = [P].[ProductId]
AND fkProductVersionFormatId = @PhysicalProductFormatId
)
WHERE P.ProductId = @BoxId
AND BL.fkLanguageId = @LanguageId --and PR.PartnerId = 8725
ORDER BY P.ProductId,
PR.PartnerId,
CP.ProductId;