Hi Guys,

I have managed to make some headway with the long running query. In the first instance I took the advice J Livingston and indexed the source tables. With this the original query dropped from 25mins to 4min 50sec.

I rewrote the script along the lines suggested by Jeff and the query ran in 3min 30 secs. I had to make a few adjustments to the Stuff statements as I checked again with the consumer of the data to find out what actually needed to be concatenated.

The final script was as follows:

WITH ctePreAggPstProdTmpl

AS

(

SELECT TemplateID

, SpecID --To be used as a join filter in another query.

, SpecIDs =

STUFF(

(

SELECT ' ; ' + Data FOR XML PATH('')),1,3,'')

FROM dbo.PstProdTmplData

WHERE SpecID IN

(

'Episode title'

,'Commercial brand'

,'Commercial product'

,'Commercial type'

,'Country of origin'

,'Year of production'

)

AND TemplateID IN (SELECT ItemID FROM dbo.InventTable) --Implicitly DISTINCT and as fast as a join.

GROUP BY TemplateID, SpecID,Data

)

--Pivot Aggregate Data

SELECT TemplateID = ISNULL(TemplateID,0) --Makes the column in the lookup table NOT NULL.

, CREpisodeTitle = MAX(CASE WHEN SpecID = 'Episode title' THEN SpecIDs ELSE '' END)

, CRComBrand = MAX(CASE WHEN SpecID = 'Commercial brand' THEN SpecIDs ELSE '' END)

, CRComProduct = MAX(CASE WHEN SpecID = 'Commercial product' THEN SpecIDs ELSE '' END)

, CRComType = MAX(CASE WHEN SpecID = 'Commercial type' THEN SpecIDs ELSE '' END)

, CRCountry = MAX(CASE WHEN SpecID = 'Country of origin' THEN SpecIDs ELSE '' END)

, CRYear = MAX(CASE WHEN SpecID = 'Year of production' THEN SpecIDs ELSE '' END)

INTO #Lookup_PstProdTmplData

FROM ctePreAggPstProdTmpl

GROUP BY TemplateID

ALTER TABLE #Lookup_PstProdTmplData

ADD PRIMARY KEY CLUSTERED (TemplateID)

;

-- CREATE CRAltNumber Lookup

SELECT N1.[CREATIONID],

STUFF

((SELECT ' ; ' + N2.CRNUMBERTYPEID + '|#|' + N2.CRNUMBER + CASE N2.CRSOCIETYCODE WHEN '0' THEN '' ELSE '|#|' + N2.CRSOCIETYCODE END

FROM CRALTNUMBERS N2

WHERE N1.CREATIONID = N2.CREATIONID FOR XML PATH('')), 1, 3, '') AS CRNumbers

INTO #Lookup_CRALTNUMBERS

FROM CRALTNUMBERS N1 WHERE N1.[CREATIONID] IN (SELECT ItemID FROM dbo.InventTable)

GROUP BY N1.[CREATIONID]

ALTER TABLE #Lookup_CRALTNUMBERS

ADD PRIMARY KEY CLUSTERED (CREATIONID)

-- CREATE CRTITLE Lookup

SELECT N1.[CREATIONID],

STUFF

((SELECT ' ; ' + N2.CRTITLE

FROM [dbo].[CRALTTITLES] N2

WHERE N1.CREATIONID = N2.CREATIONID FOR XML PATH('')), 1, 3, '') AS CRTitles

INTO #Lookup_CRALTitles

FROM [CRALTTITLES] N1 WHERE N1.[CREATIONID] IN (SELECT ItemID FROM dbo.InventTable)

GROUP BY N1.[CREATIONID]

ALTER TABLE #Lookup_CRALTitles

ADD PRIMARY KEY CLUSTERED (CREATIONID)

-- CREATE MATCHNAME Lookup

SELECT N1.[CREATIONID],

STUFF

((SELECT ' ; ' + LTRIM(N2.FIRSTNAME + ' ' + N2.NAME)

FROM [dbo].[MATCHNAME] N2

WHERE N1.CREATIONID = N2.CREATIONID FOR XML PATH('')), 1, 3, '') AS CRNames

INTO #Lookup_MatchNames

FROM [CRALTTITLES] N1 WHERE N1.[CREATIONID] IN (SELECT ItemID FROM dbo.InventTable)

GROUP BY N1.[CREATIONID]

ALTER TABLE #Lookup_MatchNames

ADD PRIMARY KEY CLUSTERED (CREATIONID)

-- CREATE BOM Parents Lookup

SELECT b1.[ITEMID],

STUFF

((SELECT ' ; ' + b2.BOMID

FROM BOM b2

WHERE b1.ITEMID = b2.ITEMID FOR XML PATH('')), 1, 3, '') AS CRParents

INTO #Lookup_BOMParents

FROM [dbo].[BOM] b1 WHERE b1.ITEMID IN (SELECT ItemID FROM dbo.InventTable)

GROUP BY b1.[ITEMID]

ALTER TABLE #Lookup_BOMParents

ADD PRIMARY KEY CLUSTERED (ITEMID)

--CREATE BOM Children Lookup

SELECT b1.BOMID,

STUFF

((SELECT ' ; ' + b2.ItemID

FROM BOM b2

WHERE b1.BOMID = b2.ITEMID FOR XML PATH('')), 1, 3, '') AS CRChildren

INTO #Lookup_BOMChildren

FROM [dbo].[BOM] b1 WHERE b1.BOMID IN (SELECT ItemID FROM dbo.InventTable)

GROUP BY b1.BOMID

ALTER TABLE #Lookup_BOMChildren

ADD PRIMARY KEY CLUSTERED (BOMID)

SELECT i.ITEMID AS CRItemID, i.PSTPRODUCTID AS CRProductID, i.NAMEALIAS AS CRName, i.CREATIONPERFORMER AS CRPerformer,

i.CREATIONDURATION AS CRDuration, i.CREATIONSTATUS AS CRStatus, i.[MODIFIEDDATETIME] AS CRModifiedDateTime,

c.CRNumbers, T.CRTitles, M.CRNames, B.CRParents, W.CRChildren, P.CREpisodeTitle, p.CRComBrand, p.CRComProduct, p.CRComType, p.CRCountry, p.CRYear

FROM dbo.InventTable i

LEFT JOIN #Lookup_PstProdTmplData p

ON p.TemplateID = i.ItemID

LEFT JOIN #Lookup_CRALTNUMBERS C

ON C.CREATIONID = i.ItemID

LEFT JOIN #Lookup_MatchNames M

ON M.CreationID = I.ItemID

LEFT JOIN #Lookup_CRALTitles T

ON T.CREATIONID = i.ItemID

LEFT JOIN #Lookup_BOMParents B

ON B.ITEMID = i.ItemID

LEFT JOIN #Lookup_BOMChildren W

ON W.BOMID = I.ITEMID

I will follow the advice of J Livingston and run within SSIS with separate SQL Tasks for each stuff component. Anymore increased performance will be a further bonus. Many thanks for the help and tips. Really helped change my mindset with this query. The comments about naming conventions have been taken on board 😉