SQLTeam.com | Weblogs | Forums

Temp table update taking too much time

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

Please post the execution plan here? Also do you have indices on the columns you are either joining or filtering on?

CountryName 
tempCountry 
MarketingSubRegion
tempMarketingSubRegion 
SubRegionISO 
tempSubRegionISOCode
RegionName 
tempRegion 
fkUniverseId 
fkBoxCountryId 
fkPartnerCountryId 
CountryId
fkSpecimenId 

etc

We're probably not going to be able to help, then.