Hello.
Would anyone be willing to pick apart the below code for inefficiencies with me? As is written now, the query takes more than 25mins to produce only 3000 rows out of a total 632,000 records. The issue lies with the last left join on PROD. The reason I put it there is because the only key I can join the data on is the [MASTER UPC#] from the pivoted dataset. Without this left join the query only takes 36 seconds to execute.
The PDWData and PDWPrice tables are vertical with non-distinct PK rows, and the outcome needs to be pivoted into a table with a distinct PK from which I can filter by the various columns using a WHERE clause.
SELECT *
FROM (
SELECT [PDWDataID], META.[Description], [DataValue], [VEN PRICE EFF DATE], [PRICE PER EA], [PRICE PER C], [PRICE PER DZ]
FROM [dbo].[PDWData] PDWDATA
LEFT JOIN DBO.PDWMeta META ON META.PDWMetaID = PDWDATA.MetaID
LEFT JOIN (
SELECT [PDWPriceID],
MAX([EffectiveDate]) AS 'VEN PRICE EFF DATE',
Min(Case [PricePerUnit] When 'EA' Then [Value] End) 'PRICE PER EA',
Min(Case [PricePerUnit] When 'C' Then [Value] End) 'PRICE PER C',
Min(Case [PricePerUnit] When 'DZ' Then [Value] End) 'PRICE PER DZ'
--Min(Case [PricePerUnit] When 'PR' Then [Value] End) 'PricePerUnit PR',
--Min(Case [PricePerUnit] When 'PK' Then [Value] End) 'PricePerUnit PK',
--Min(Case [PricePerUnit] When 'BX' Then [Value] End) 'PricePerUnit BX',
--Min(Case [PricePerUnit] When 'CS' Then [Value] End) 'PricePerUnit CS',
--Min(Case [PricePerUnit] When 'BG' Then [Value] End) 'PricePerUnit BG',
--Min(Case [PricePerUnit] When 'IP' Then [Value] End) 'PricePerUnit IP',
--Min(Case [PricePerUnit] When 'UN' Then [Value] End) 'PricePerUnit UN',
--Min(Case [PricePerUnit] When 'DOZEN' Then [Value] End)'PricePerUnit DOZEN',
--Min(Case [PricePerUnit] When 'CA' Then [Value] End) 'PricePerUnit CA',
--Min(Case [PricePerUnit] When 'ST' Then [Value] End) 'PricePerUnit ST',
--Min(Case [PricePerUnit] When 'KT' Then [Value] End) 'PricePerUnit KT',
--Min(Case [PricePerUnit] When 'E' Then [Value] End) 'PricePerUnit E',
--Min(Case [PricePerUnit] When 'FT' Then [Value] End) 'PricePerUnit FT',
--Min(Case [PricePerUnit] When 'M' Then [Value] End) 'PricePerUnit M',
--Min(Case [PricePerUnit] When 'DZN' Then [Value] End)'PricePerUnit DZN',
--Min(Case [PricePerUnit] When 'DP' Then [Value] End) 'PricePerUnit DP',
--Min(Case [PricePerUnit] When 'KIT' Then [Value] End) 'PricePerUnit KIT',
--Min(Case [PricePerUnit] When 'DS' Then [Value] End) 'PricePerUnit DS',
--Min(Case [PricePerUnit] When 'BLK' Then [Value] End) 'PricePerUnit BLK',
--Min(Case [PricePerUnit] When 'EACH' Then [Value] End) 'PricePerUnit EACH',
--Min(Case [PricePerUnit] When 'PACK' Then [Value] End) 'PricePerUnit PACK',
--Min(Case [PricePerUnit] When 'RL' Then [Value] End) 'PricePerUnit RL',
--Min(Case [PricePerUnit] When 'V' Then [Value] End) 'PricePerUnit V',
--Min(Case [PricePerUnit] When 'BOX' Then [Value] End) 'PricePerUnit BOX',
--Min(Case [PricePerUnit] When 'ROLL' Then [Value] End) 'PricePerUnit ROLL',
--Min(Case [PricePerUnit] When 'CAS' Then [Value] End) 'PricePerUnit CAS'
FROM dbo.PDWPrice PDWP
WHERE EffectiveDate = (SELECT MAX(EffectiveDate) FROM PDWPrice Price WHERE Price.PDWPriceID = PDWP.PDWPriceID and Price.PricePerUnit = PDWP.PricePerUnit)
GROUP BY [PDWPriceID]) PDWP ON PDWP.[PDWPriceID] = PDWDATA.PDWDataID) PDWD
PIVOT(max(PDWD.[DataValue]) FOR PDWD.[Description] in (
[VEN BRAND],
[MASTER UPC#],
[UPC 12],
[VEN CATALOG NUMBER],
[VEN PART NUMBER],
[VEN MODEL NUMBER],
[VEN G/L CODE],
[VEN PRICE LINE],
[VEN BUY LINE],
[VEN BUY PACKAGE],
[VEN MIN QTY],
[VEN SELL GROUP],
[VEN STATUS],
[VEN COMMODITY CODE],
[VENDOR DISCOUNT CLASS],
[BUYER],
[NEFCO PART TYPE],
[VEN DESCRIPTION 1],
[VEN DESCRIPTION 2],
[VEN LONG DESCRIPTION],
[VEN DESCRIPTION],
[VEN KEYWORD 1],
[VEN KEYWORD 2],
[VEN KEYWORD 3],
[VEN KEYWORD 4],
[VEN KEYWORD 5],
[VEN KEYWORD 6],
[VEN KEYWORD 7],
[VEN KEYWORD 8],
[VEN KEYWORD 9],
[VEN KEYWORD 10],
[VEN KEYWORD 11],
[VEN KEYWORD 12],
[VEN KEYWORD 13],
[VEN KEYWORD 14],
[VEN KEYWORD 15],
[VEN KEYWORD 16],
[VEN PRODUCT CATEGORY],
[VENDOR CATEGORY],
[VEN SUB CATEGORY],
[VEN PRODUCT FAMILY],
[VEN GROUP],
[VEN SEGMENT],
[VEN PRODUCT CLASS],
[VEN QTY PER PKG],
[VEN_DIMENSIONS],
[VEN LENGTH],
[VEN HEIGHT],
[VEN DEPTH],
[VEN WIDTH],
[VEN WEIGHT],
[VEN STD PKG WEIGHT],
[VEN CARTON UPC12],
[VEN_CTN_QTY],
[VEN CARTON QTY],
[VEN CARTON WIDTH],
[VEN CARTON LENGTH],
[VEN CARTON HEIGHT],
[VEN CARTON WEIGHT],
[VEN PALLET UPC12],
[VEN PALLET QTY],
[VEN PALLET LENGTH],
[VEN PALLET WIDTH],
[VEN PALLET HEIGHT],
[VEN PALLET WEIGHT],
[VEN FINISH],
[VEN MATERIAL],
[VEN COLOR],
[VEN SIZE],
[VEN STYLE],
[VEN IMAGE],
[VEN WEB LINK],
[VEN WEB LONG DESCRIPTION],
[VEN WEB SHORT DESCRIPTION],
[VEN GOOGLE DDE AGENT],
[VEN GOOGLE PRODUCT SEARCH],
[VEN GOOGLE DATA]
)
) as PVT
LEFT JOIN (SELECT ProductID, UpcCode, ProductDesc1 as 'ECL DESCRIPTION 1', concat(ProductDesc2, ProductDesc3, ProductDesc4, ProductDesc5, ProductDesc6, ProductDescExt) as 'ECL DESCRIPTION 2-10' FROM Product) PROD on PROD.[UpcCode] = [MASTER UPC#]
Thank you!