Query Optimization and Performance Issue

Transact-SQL
1

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)

Find execution plan as well if you need
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

2

It seems you have problem catch-all queries and this one looks more complex than the last one you posted.
You should really look at the whole SP to solve the problems. As far as this snippet of code is concerned I would avoid joins in sub-queries as they tend to resolve to nested loops. In this case try using EXISTS to get rid of the JOIN and/or moving the logic to an OUTER APPLY.

You should probably start reading about catch-all queries. You may want to start with:

http://www.sommarskog.se/dyn-search-2008.html

3

hi

Looking at it

Two possible suspects

  1. top 1
  2. case when statement

How do you know which one
Please see the cost in the execution plan !!!

Hope this helps
:slight_smile:

Once you identify cause
Solutions can be thought of NEXT !!

4

looking at the execution plans shows 2 things. The KeyLookup on ProductCountry. You can modify the IX_ProductCountry_PorductCountryID index to include columns used in this key lookup. Also, performing scan on the Market region table is not good.

The obvious issue is this part. The nested Case Statement has to compare each row. I would look into reworking this. If you can provide DDL and sample data, we could provide more assistance.

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

5

As Mike
says case statement is the problem

One idea for case statement is

As an alternate way ...

Create a lookup table of case values
And join to the lookup table

Hope this helps

6

Hi,
Please find attached the complete query

DECLARE @BoxId int =1471
DECLARE @PackagingSectionId int = 1
DECLARE @LanguageId int = 77

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @noLocation varchar(20) = 'Hide phone and email' --'noLocation'
declare @hiddenContact varchar(20) = 'Hide map localisation' --'hiddenContact'
declare @PhysicalProductFormatId int = 1
declare @BoxUniverse int
declare @BoxCode int, @BoxCodeS int, @BoxVersionS int
declare @BoxCountryId int = (SELECT DISTINCT fkCountryId FROM ProductCountry WHERE fkProductId = @BoxId)
declare @MasterBoxCode int

declare @ItalyCountryId INT = 109
declare @RomaMarketingRegionId INT = (SELECT MarketingRegionId FROM MarketingRegion WHERE MarketingRegionName = 'Roma')
declare @MilanoMarketingRegionId INT = (SELECT MarketingRegionId FROM MarketingRegion WHERE MarketingRegionName = 'Milano')
declare @WellnessUniverseId INT = 8
declare @GastronomyUniverseId INT = 2

select @MasterBoxCode = dbo.udf_GetMasterProductByProductId(@BoxId)

select @BoxCode = dbo.udf_GetPhysicalProductIdForLanguage(@BoxId, @LanguageId),
@BoxCodeS = dbo.udf_GetLatestSpecimenVersionId(@BoxId),
@BoxVersionS = dbo.udf_GetLatestSpecimenVersion(@BoxId)

select @BoxUniverse = fkUniverseId from Product inner join ProductUniverse on Product.ProductId = ProductUniverse.fkProductId
where Product.ProductId = @BoxId

if @BoxUniverse != 6
Begin
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,
CASE WHEN fvloc.value=1 THEN null ELSE PL.AddressLine1 END as AddressLine1,
PL.AddressLine2,PL.PostCode,PL.City,
PL.SubRegion as ParterSubRegion,
PartnerRegion = case when isnull(C.CountryName,'') <> PL.Country then isnull(CL.CountryName,PL.Country) else PL.Region end,
PL.Country,
CASE WHEN fv.value=1 THEN null
ELSE CASE WHEN left(isnull(PL.PhoneNo,''),1) <> '0' and PL.PhoneNo is not null THEN '0'+ PL.PhoneNo
ELSE PL.PhoneNo
END
END as PhoneNo,
CASE WHEN fv.value = 1 THEN null ELSE PL.Email END as Email,
PL.Website,PL.Longitude,PL.Latitude,
CanBeBooked = isnull((SELECT top 1 P.isReservable FROM Product P
JOIN RelatedProduct RP ON P.ProductId = RP.fkProduct1Id
WHERE RP.fkRelationTypeId = 40 AND RP.fkProductId = CP.ProductId
and P.isReservable = 1 ),0),
PL.MarketingRegion as LocationName,
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,
MarketingRegionClassification = case when isnull(C.CountryName,'') <> PL.Country then isnull(CL.CountryName,PL.Country) else MRU.MarketingRegion END,
ExperienceWeb = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 1) then 'Yes' else 'No' end,
ExperienceSpecimen = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 3) then 'Yes' else 'No' end,
ExperienceBooklet = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 4) then 'Yes' else 'No' end,
SubRegionNumber = substring(isnull(PL.SubRegionISOCode,''), 4, 10) ,
BoxCode = @BoxCode,
BoxCodeS = @BoxCodeS,
BoxVersionS = @BoxVersionS,
MasterBoxCode = @MasterBoxCode,
MinNumberOfPeople = (select top 1 FV.Value from ProductFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 45 and PFV.fkProductId = CP.ProductId),
MaxNumberOfPeople = (select top 1 FV.Value from ProductFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 44 and PFV.fkProductId = CP.ProductId),
[Michelin Rating] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 61 and PFV.fkPartnerId = PR.PartnerId),''),
[TripAdvisor Rating] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 35 and PFV.fkPartnerId = PR.PartnerId),''),
[TripAdvisor Certificate of Excellence] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 34 and PFV.fkPartnerId = PR.PartnerId),''),
[TripAdvisor Travellers Choice] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 36 and PFV.fkPartnerId = PR.PartnerId),''),
[Logis de France] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 23 and PFV.fkPartnerId = PR.PartnerId),''),
[Gault et Millaud] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 14 and PFV.fkPartnerId = PR.PartnerId),''),
[Bib gourmand] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 62 and PFV.fkPartnerId = PR.PartnerId),''),
[Plate] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 63 and PFV.fkPartnerId = PR.PartnerId),''),
[Hotel di charme] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 18 and PFV.fkPartnerId = PR.PartnerId),''),

   [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
	-- END Modification DB
   Inner Join ProductUniverse PU  with (nolock) on PU.fkProductId = P.ProductId
   left join ProductBrand PB with (nolock) on PB.fkProductId= P.ProductId
   left join Brand B with (nolock) on B.BrandId=PB.fkBrandId
   Left Join Universe BU  with (nolock) on BU.UniverseId = P.fkUniverseBoxId
   Left Join MarketingRegionUniverse MRU with (nolock) on MRU.UniverseId = P.fkUniverseBoxId and MRU.SubregionISOCode = PL.SubRegionISOCode
   Inner Join Product CP  with (nolock) on CP.ProductId = RP.fkProduct1Id and CP.fkTriggerStateId = 4
   Inner Join ProductUniverse CPU  with (nolock) on CPU.fkProductId = CP.ProductId
   Inner Join Universe EU  with (nolock) on EU.UniverseId = CPU.fkUniverseId
   Inner Join ProductLanguage BL  with (nolock) on BL.fkProductId = P.ProductId
   Left join Country PLC with (nolock) on PLC.CountryName = PL.Country
   Left join CountryLocale CL with (nolock) on CL.fkCountryId = PLC.CountryId and BL.fkLanguageId = CL.fkLanguageId
   Inner Join Language BXL  with (nolock) on BXL.LanguageId= BL.fkLanguageId
   Left join ProductCountry PC  with (nolock) on PC.fkProductId = P.ProductId
   Left join Country C  with (nolock) on C.CountryId = PC.fkCountryId
   Inner join ProductDataTemplate PDT  with (nolock) on PDT.fkProductId = p.ProductId AND PDT.fkProductTypeId = 2 and PDT.fkUniverseId is null
   Inner join ProductDataTemplate PDPT  with (nolock) on PDPT.fkProductId = p.ProductId AND PDPT.isPartnerTemplate = 1 and PDPT.fkUniverseId is null
   Inner Join 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 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 facet f on f.FacetName = @hiddenContact
   Left join PartnerFacetValue PFV on PFV.fkPartnerId = ppt.fkPartnerId and pfv.fkFacetId = f.facetid
   Left join facetValue fv on fv.facetvalueid = pfv.fkfacetvalueid
   Left join facet floc on floc.FacetName = @noLocation
   Left join PartnerFacetValue PFVloc on PFVloc.fkPartnerId = ppt.fkPartnerId and PFVloc.fkFacetId = floc.facetid
   Left join facetValue fvloc on fvloc.facetvalueid = PFVloc.fkfacetvalueid
   Left Join (Select sum( ps.rank) as ExperienceRank,RPPS.fkRelatedProductId from RelatedProductPackagingSection RPPS with (nolock)                          
                       inner join packagingsection ps with (nolock) on ps.packagingsectionid= rpps.fkpackagingsectionid group by RPPS.fkRelatedProductId ) as ERank on rp.RelatedProductId = Erank.fkRelatedProductId
   LEFT JOIN [ProductVersion] WITH (NOLOCK) ON [ProductVersion].[fkProductId] = [P].[ProductId] AND [ProductVersion].[ProductVersionId] = (SELECT MAX([ProductVersionId]) FROM [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 
   
   create nonclustered index idx_tempindex on #BoxExtract2  (PartnerCode, ExperienceCount) 
   create nonclustered index idx_tempindex2 on #BoxExtract2  ([IndexPageNumber]) INCLUDE ([PartnerId]) 

   delete b1 
   from #BoxExtract2 b1
   join #BoxExtract2 b2 on b2.PartnerId=b1.PartnerId and b2.IndexPageNumber is not null 
   where b1.IndexPageNumber is null

   select a.*,isnull(PTA.ProductContents,'') ExperienceA_Contents,isnull(PTB.ProductContents,'') ExperienceB_Contents,isnull(PTC.ProductContents,'') ExperienceC_Contents,isnull(PTT.PartnerContents,'') Partner_Contents from (
   Select distinct 
   ShortName, 
   MasterBoxCode,
   BoxCode, 
   BoxCodeS, 
   BoxVersion, 
   BoxVersionS,
   [IndexPageNumber],
   [BoxFormat],
   IndexLayout,
   PartnerPagesLayout,
   BoxBrand, 
   BoxCountry,
   BoxLanguage,
   BoxUniverse,
   [BrandPartnership],
   [BoxTitle],
   ExperienceUniverse = isnull((select top 1 B2.UniverseName from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
   Web = isnull((select top 1 B2.ExperienceWeb from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceWeb = 'Yes'),'No') ,
   Specimen = isnull((select top 1 B2.ExperienceSpecimen from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceSpecimen = 'Yes'),'No') ,
   Booklet = isnull((select top 1 B2.ExperienceBooklet from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceBooklet = 'Yes'),'No') ,       
   PartnerId, PartnerName,PartnerCode,replace(isnull(AddressLine1,''),',','') as AddressLine1,isnull(AddressLine2,'') as AddressLine2,isnull(PostCode,'') as PostCode,isnull(City,'') as City,
   isnull(PartnerRegion,'') as RegionName,
   isnull(ParterSubRegion,'') AS SubRegionName /*as [Parnter Subregion Name]*/,
   SubRegionNumber,
   isnull(Country,'') as Country,
   PhoneNo as 'PhoneNumber',
   isnull(Email,'') as Email,isnull(Website,'') as Website,Longitude,Latitude,
   CanBeBooked = cast(isnull((select top 1 B2.CanBeBooked from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),0) as int) ,
   MaxNumberOfPeople = isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) order by B2.MaxNumberOfPeople),'') ,
   MinNumberOfPeople = isnull((select top 1 B2.MinNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) order by B2.MinNumberOfPeople),'') ,                     
   ExperienceA_Id = isnull((select top 1 B2.ExperienceId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
   ExperienceA_Code = isnull((select top 1 B2.ProductCode from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
   ExperienceA_Template = isnull((select top 1 B2.ProductTemplateId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,          
   ExperienceB_Id = isnull((select top 1 B2.ExperienceId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,
   ExperienceB_Code = isnull((select top 1 B2.ProductCode from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,
   ExperienceB_Template = isnull((select top 1 B2.ProductTemplateId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,          
   ExperienceC_Id = isnull((select top 1 B2.ExperienceId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
   ExperienceC_Code = isnull((select top 1 B2.ProductCode from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
   ExperienceC_Template = isnull((select top 1 B2.ProductTemplateId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
   ExperienceA_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1) is not null then 'Pour ' + isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'0') + ' personne' + case when isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),          
   ExperienceB_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2) is not null then 'Pour ' + isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'0') + ' personne' + case when isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),
   ExperienceC_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3) is not null then 'Pour ' + isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'0') + ' personne' + case when isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),
   isnull(PartnerTemplateId,'') as PartnerTemplateId,
   isnull(MarketingRegionClassification,'') as [Marketing Region Classification],
   [Michelin Rating],[TripAdvisor Rating],[TripAdvisor Certificate of Excellence],[TripAdvisor Travellers Choice],[Logis de France],[Gault et Millaud],
   [Bib gourmand],[Plate],[Hotel di charme]
   From #BoxExtract2 BOX
   ) a 
   left join ProductTemplate PTA on PTA.ProductTemplateId = a.ExperienceA_Template
   left join ProductTemplate PTB on PTB.ProductTemplateId = a.ExperienceB_Template
   left join ProductTemplate PTC on PTC.ProductTemplateId = a.ExperienceC_Template
   left join PartnerTemplate PTT on PTT.PartnerTemplateId = a.PartnerTemplateId
   where (case when @PackagingSectionId = 3 and a.Specimen = 'Yes' then 1
                       when @PackagingSectionId = 4 and a.Booklet = 'Yes' then 1 
                       when @PackagingSectionId = 1 and a.Web = 'Yes' then 1
                       else 0 end) = 1
   order by PartnerCode

   end
7

ooking 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

8

I would look into breaking this into smaller chunks. The code you pasted in is too long to even start with. this code here is part of your select statement. For every row it returns, it has to run through all those sub-select statements. Not very efficient. But without DDL and sample data, we can only provide hints.

MinNumberOfPeople = (select top 1 FV.Value from ProductFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 45 and PFV.fkProductId = CP.ProductId),
MaxNumberOfPeople = (select top 1 FV.Value from ProductFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 44 and PFV.fkProductId = CP.ProductId),
[Michelin Rating] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 61 and PFV.fkPartnerId = PR.PartnerId),''),

9

Thanks Mike,

How to provide sample data? I already provide DDL, what else do you want me to provide?

10

hi

PLEASE PROVIDE ... INSERT DATA SCRIPT
HOPE THIS HELPS :slight_smile:

EXAMPLE ...
INSERT INTO TABLE_NAME SELECT 'JEFF'
INSERT INTO TABLE_NAME SELECT 'PAM'
INSERT INTO TABLE_NAME SELECT 'SAMUEL'

11

Harish,
There are so more than 20 tables involved, how to send insert script?

12

oh ok

EXAMPLE ...IF THE QUERY IS LIKE THIS ....

SELECT
TABLE1.NAME , TABLE2.AGE
FROM
TABLE1
JOIN
TABLE2
ON TABLE1.ID = TABLE2.ID

INSERT INTO TABLE1 (ID,NAME) SELECT 1 , 'JEFF'
INSERT INTO TABLE1 (ID,NAME) SELECT 2 , 'PAM'

INSERT INTO TABLE2 (ID,AGE) SELECT 1 , 40
INSERT INTO TABLE2 (ID,AGE) SELECT 2 , 30

13

Hi Ifor,

If i comment this line performance improve quite a bit, any suggestions please

left JOIN MasterLocations ml3 ON ml3.RegionName = PL.Region

14

hope this helps :slight_smile:

create indexes on
ml3.RegionName and PL.Region

remove join !!!! do the join after everything else seperately

or split the data involved into small bits
if data 10 million .. 50 thousand rows at a time .. !!!!

15

Thanks Harish

I created the index already but no luck, data returned is around 10k only...

CREATE NONCLUSTERED INDEX [IDX_MasterLocations_RegionName] ON [dbo].[MasterLocations]
(
[RegionName] ASC
)
INCLUDE([MasterLocationsId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IDX_PartnerLocations_Region] ON [dbo].[PartnerLocation]
(
[Region] ASC
)
INCLUDE([PartnerLocationId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

What do you mean by do the join seperately?

16

What data type are those 2 columns

17

How do you determine which value you want from this
Seems so arbitrary
select top 1 FV.Value from ProductFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 45

18

Its a production SP, someone wrote long back, my concern is not the functionality or data but the performance

19

I suspect you could comment other lines and get a performance improvement; uncommenting them just pushes the optimizer over the edge.

As you know, this code is a mess and is likely to take days to sort out; not something doable on a forum.
The code is far too complex for the optimizer to deal with and ideally needs to be broken down. There are also some bad code smells:

  1. NOLOCK (READ UNCOMMITTED) - Do you really want phantom reads, error 601 etc in a production environment? I suspect this has been done in an attempt to reduce blocking. Sorting out the code is the correct way to do this.

  2. SELECT TOP (1) without an ORDER BY. This suggests the result could be undeterministic.

etc

My initial approach would be:

  1. Look at reducing JOINs by pivoting some tables.
  2. Look at creating indexed temp tables from no more than seven of the original joins. These can then be joined at the end in a final query. It may also be possible to limit the size of some temp tables by doing exists queries on previous temp tables.

I would not bother with indexes at this stage as I suspect part of the blocking problem is that someone has mindlessly run the index tuning wizard which is fine for SELECTs but not transaction throuhput.

Long term you could look at a reporting database/cube but this would require analyzing all your reporting queries.

Pro Tem, if you need a quick temporary fix, you could look at going through all the joins and specifying the type of join used in the query plan.

eg

SELECT *
FROM Product P
	INNER JOIN RelatedProduct RP
		ON RP.fkProductId = P.ProductId
			AND RP.Active = 1

May produce a MERGE JOIN in the query plan so you then specify:

FROM Product P
	INNER MERGE JOIN RelatedProduct RP
		ON RP.fkProductId = P.ProductId
			AND RP.Active = 1

and

SELECT *
FROM RelatedProduct RP
	INNER JOIN PartnerProduct PP
		ON PP.fkProductId = RP.fkProduct1Id
			AND PP.Active = 1

might produce a HASH join in the query plan so the FROM clause now becomes:

FROM Product P
	INNER MERGE JOIN RelatedProduct RP
		ON RP.fkProductId = P.ProductId
			AND RP.Active = 1
	INNER HASH JOIN PartnerProduct PP
		ON PP.fkProductId = RP.fkProduct1Id
			AND PP.Active = 1

etc

ie You bypass the optimizer for the full query with the MERGE/HASH hints etc.

This is not maintainable long term as the query will tank as soon as the data distribution changes.

Good luck!!

20

Ha! It might be blazing fast if you uncomment code out but it wont be functional.