SQL Query performance optimization

Transact-SQL
1

I have been asked to improve performance of below SP which seems horrible. Can anyone provide any suggestions please?

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 = 24 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
2

There are alot of issues with this. Do you have an execution plan?

At first glance, I'd look at all of these, but I would imagine you are doing table scans and each of the columns is doing a select for each row returned, so that makes it even worse. Also, DDL and sample data would help

[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])

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

3

scary.

What kind of performance do you get if you temporarily removed the below section?

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 = 24 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),''),
4

hi

one suggestion
when you a have a big long query ... thats running too slow

break it up into tiny bits that run fast .... may be add indexes to the tiny bits
to make it run even faster ...

if you have 10 joins ... do 2 or 3 joins at a time ...

first join1 to join2 ... result12
then result12 join to join3 .. result13

like this

another thing
select * from tablea join tableb on tablea.name = tableb.name where tablea.name = 'xyz'
if you have where clauses

first you can put into temp tables the where clauses !!!
select * into #xxx from tablea where name = 'xyz'
then join
select * from #xxx join tableb

hope this helps
:slight_smile: :slight_smile:

5

mostly it about the amount of data thats being handled

example
select * from table a join table b on a.id = b.id join table c on a.id = c.id

here lets say
Scenario 1
table a has 1 million rows
and table b has 3 rows
and table c has 10 000 rows

OR

Scenario 2
table a has 100 000 rows
and table b has 10 million rows
and table c has 20 million rows

the approach would depend on this data layout

its like
10 rows times 10 rows = 100 rows ( 2 sec for each row so 200 sec )
or
10 rows time 1 rows = 10 rows ( 2 sec for each row so 10 sec )
(if you can filter the data 10 rows to 1 row with where clause
and then do the join )

hope this helps :slight_smile: :slight_smile:
as usual experts here on this forum please share your valuable insights !!!

6

The result set of your query is already send to a local temporary table.

 SELECT ....
 INTO #BoxExtract2

You can use that temp table to gradualy give more and more columns their final value. But you won't do it with one monolith query, but step by step. The first query will populate the temp table with the final numer of records, but with a number of (placeholder) columns that are initially still NULL. After that, you write a number of UPDATE statements to give those placeholder columns their final value.

The query contains many occurences like:

,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
		)

When you have many of these, they have a tremendous negatively impact on performance.
Replace them by NULL's with the right data type:

 CAST(NULL as INT) AS MaxNumberOfPeople

Then throw away all the LEFT OUTER JOINs.
SQL Sever will throw an error each time you reference a column from a table that is not JOINed any longer, like

	,BU.UniverseName AS BoxUniverse

As before, replace them by NULL's with the right data type:

 ,CAST(NULL as VARCHAR(50)) AS BoxUniverse

(you may have to use VARCHAR(5) or VARCHAR(50) or VARCHAR(255) or ...)

Run your query to populate the temp table. Check if the speed is accaptable. If not, tweak the query, indexes, ... untill performance s good. (Als long as perfomance is good, you may add LEFT OUTER JOINS until performance degrades.)

Next, give the columns that you perviously have set to NULL, their final values by using UPDATE statements.

Denormalising is also bad for performance.

,[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, '')

If your SQL Server version is 2017 (or later) replace it by using the STRING_AGG() function.

This is your SQL; prettified to enhence readability:

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 = 24
				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
7

Thanks mike,
I do have execution plan however system not allowing me to update it :frowning: