question

ksarfraz avatar image
ksarfraz asked

Query Optimization and Performance Issue

Hi,

Production SP is performing very poor and even sometime application is getting timed out. I looked at the SP and found out that query below highlighted in BOLD is the mail cause of concerns. Any chance anyone can look and provide any inputs whats wrong in this query and why its taking huge time. Thanks in advance. (If you want script for whole SP then leave comment and i will paste here)

[IndexPageNumber] = ( SELECT TOP 1 NumberOfIndexPages FROM [EditionBrief] INNER JOIN [EditionBriefRegionalIndexPages] ON [EditionBriefRegionalIndexPages].[fkEditionBriefId] = [EditionBrief].[EditionBriefId] WHERE [fkBoxId] = [P].[ProductId] AND fkMarketingRegionsId =

CASE WHEN cntry.CountryId <> @BoxCountryId THEN mr2.MarketingRegionId WHEN (@BoxCountryId = @ItalyCountryId AND BU.UniverseId IN (@WellnessUniverseId, @GastronomyUniverseId)) THEN CASE PL.City WHEN 'Roma' THEN @RomaMarketingRegionId WHEN 'Milano' THEN @MilanoMarketingRegionId ELSE mr1.MarketingRegionId END ELSE mr1.MarketingRegionId END AND EditionBrief.fkSpecimenId = @BoxCodeS



Execution Plan

https://www.brentozar.com/pastetheplan/?id=BydkTId-I https://www.brentozar.com/pastetheplan/?id=ByeEpUu-U https://www.brentozar.com/pastetheplan/?id=HkYU0Ud-I https://www.brentozar.com/pastetheplan/?id=B1Qi0UOZI


performance-tuning
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

None of those execution plans match the query you've posted?

0 Likes 0 ·

1 Answer

·
ksarfraz avatar image
ksarfraz answered

looking deep down at the query i can see problem is below lef t join on Masterlocation table. Any suggestions how to avoid left join to same table without impacintg the query output?

left join MasterLocations ml1 ON ml1.MarketingSubRegion = PL.MarketingSubRegion
left JOIN MasterLocations ml2 ON ml2.SubRegionISO = PL.SubRegionISOCode
left JOIN MasterLocations ml3 ON ml3.RegionName = PL.Region

[IndexPageNumber] = (
SELECT TOP 1 NumberOfIndexPages FROM [EditionBrief]
INNER JOIN [EditionBriefRegionalIndexPages] ON [EditionBriefRegionalIndexPages].[fkEditionBriefId] = [EditionBrief].[EditionBriefId]
WHERE [fkBoxId] = [P].[ProductId] AND fkMarketingRegionsId =

CASE
WHEN cntry.CountryId <> @BoxCountryId THEN mr2.MarketingRegionId
WHEN (@BoxCountryId = @ItalyCountryId AND BU.UniverseId IN (@WellnessUniverseId, @GastronomyUniverseId)) THEN
CASE PL.City
WHEN 'Roma' THEN @RomaMarketingRegionId
WHEN 'Milano' THEN @MilanoMarketingRegionId
ELSE mr1.MarketingRegionId
END
ELSE mr1.MarketingRegionId
END
AND EditionBrief.fkSpecimenId = @BoxCodeS
),
[BoxFormat] = STUFF((SELECT ',' + [Format].[Name] FROM [AvailableProductFormat] INNER JOIN [Format] ON [Format].[FormatId] = [AvailableProductFormat].[fkFormatId] WHERE [fkProductId]=[P].[ProductId] ORDER BY [PriorityForConcept] ASC FOR XML PATH('')),1,1,''),
[IndexLayout] = (SELECT TOP 1 edb.IndexLayout FROM EditionBrief as edb WHERE fkBoxId = @BoxId AND fkSpecimenId = @BoxCodeS),
[PartnerPagesLayout] = (SELECT TOP 1 edb.PartnerPagesLayout FROM EditionBrief as edb WHERE fkBoxId = @BoxId),
[BrandPartnership] = (SELECT ISNULL([BrandPartnership].[Name],'none') FROM [Product] LEFT JOIN [BrandPartnership] ON [BrandPartnership].[BrandPartnershipId] = [Product].[fkBrandPartnershipId] WHERE [Product].[ProductId] = [P].[ProductId])
Into #BoxExtract2
From Product P
Inner Join RelatedProduct RP with (nolock) on RP.fkProductId = P.ProductId and RP.Active = 1
Inner Join PartnerProduct PP with (nolock) on PP.fkProductId = RP.fkProduct1Id and PP.Active = 1
Inner Join Partner PR with (nolock) on PR.PartnerId = PP.fkPartnerId and PR.PartnerStatus = 1
Inner Join PartnerLocation PL with (nolock) on PL.fkPartnerId = PR.PartnerId
-- BEGINNING Modification DB
inner join Country cntry ON cntry.CountryName = PL.Country
left join MasterLocations ml1 ON ml1.MarketingSubRegion = PL.MarketingSubRegion
left JOIN MasterLocations ml2 ON ml2.SubRegionISO = PL.SubRegionISOCode
left JOIN MasterLocations ml3 ON ml3.RegionName = PL.Region

left join MarketingRegionClassification mrc on mrc.fkUniverseId = @BoxUniverse and mrc.fkBoxCountryId = @BoxCountryId AND mrc.fkMasterLocationId IS NULL AND mrc.fkPartnerCountryId = cntry.CountryId

left join MarketingRegionClassification mrc2 on mrc2.fkUniverseId = @BoxUniverse and mrc2.fkBoxCountryId = @BoxCountryId
AND mrc2.fkMasterLocationId = isnull(ml1.MasterLocationsId,isnull(ml2.MasterLocationsId,ml3.MasterLocationsId)) AND mrc2.fkPartnerCountryId = cntry.CountryId

left join MarketingRegionClassification mrc3 on mrc3.fkUniverseId = @BoxUniverse and mrc3.fkBoxCountryId = @BoxCountryId AND mrc3.fkMasterLocationId is null AND mrc3.fkPartnerCountryId = cntry.CountryId
left join MarketingRegion mr1 on mr1.MarketingRegionId = mrc2.fkMarketingRegionId
left join MarketingRegion mr2 on mr2.MarketingRegionId = mrc3.fkMarketingRegionId

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.