question

ksarfraz avatar image
ksarfraz asked

How to avoid left join

There are lots of left join in below query which is giving performance issue, I want to remove the left joins on MasterLocations Table. Any suggestion please?

    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) -- 109
declare @MasterBoxCode int
declare @ItalyCountryId INT = 109
declare @RomaMarketingRegionId INT = (SELECT MarketingRegionId FROM MarketingRegion WHERE MarketingRegionName = 'Roma')--12
declare @MilanoMarketingRegionId INT = (SELECT MarketingRegionId FROM MarketingRegion WHERE MarketingRegionName = 'Milano')--11
declare @WellnessUniverseId INT = 8
declare @GastronomyUniverseId INT = 2
select @MasterBoxCode = dbo.udf_GetMasterProductByProductId(@BoxId) --1471
select@BoxCode = dbo.udf_GetPhysicalProductIdForLanguage(@BoxId, @LanguageId), -- 1471
@BoxCodeS = dbo.udf_GetLatestSpecimenVersionId(@BoxId), -- 489191
@BoxVersionS = dbo.udf_GetLatestSpecimenVersion(@BoxId) --2
--PRINT @BoxCode
--PRINT @BoxCodeS
--PRINT @BoxVersionS
select @BoxUniverse = fkUniverseId  from Product inner join ProductUniverse on Product.ProductId = ProductUniverse.fkProductId 
where Product.ProductId = @BoxId -- 4
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

performance-tuning
10 |1200

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

0 Answers

·

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.