Hi,
I am updating temp table based on some condition and execution plan says it is taking 70% of time in the batch. Believe some index on temp table or any other table can sort this out. Any suggestions please
Not able to attach the plan, below is the code
UPDATE T SET T.IndexPageNumber = (
SELECT TOP 1 NumberOfIndexPages FROM [dbo].[EditionBrief]
INNER JOIN [dbo].[EditionBriefRegionalIndexPages] ON [dbo].[EditionBriefRegionalIndexPages].[fkEditionBriefId] = [dbo].[EditionBrief].[EditionBriefId]
INNER JOIN Country cntry on cntry.CountryName = T.tempCountry
LEFT JOIN [dbo].MasterLocations ml1
ON ml1.MarketingSubRegion = T.tempMarketingSubRegion
LEFT JOIN [dbo].MasterLocations ml2
ON ml2.SubRegionISO = T.tempSubRegionISOCode
LEFT JOIN [dbo].MasterLocations ml3
ON ml3.RegionName = T.tempRegion
LEFT JOIN [dbo].MarketingRegionClassification mra
ON mra.fkUniverseId = @BoxUniverse
AND mra.fkBoxCountryId = @BoxCountryId
AND mra.fkMasterLocationId IS NULL
AND mra.fkPartnerCountryId = cntry.CountryId
LEFT JOIN [dbo].MarketingRegionClassification mra2
ON mra2.fkUniverseId = @BoxUniverse
AND mra2.fkBoxCountryId = @BoxCountryId
AND mra2.fkMasterLocationId = ISNULL(ml1.MasterLocationsId,ISNULL(ml2.MasterLocationsId,ml3.MasterLocationsId)) AND mra2.fkPartnerCountryId = cntry.CountryId
WHERE [fkBoxId] = T.[tempProductId] AND fkMarketingRegionsId =
CASE
WHEN cntry.CountryId <> @BoxCountryId THEN mra.fkMarketingRegionId
WHEN @BoxCountryId <> @ItalyCountryId THEN mra2.fkMarketingRegionId
ELSE
CASE T.tempCity
WHEN 'Roma' THEN @RomaMarketingRegionId
WHEN 'Milano' THEN @MilanoMarketingRegionId
ELSE mra2.fkMarketingRegionId
END
END
AND EditionBrief.fkSpecimenId = @BoxCodeS
) FROM #BoxExtract2 T