I have written the below and am seeking any way that I could improve its performance.
IF OBJECT_ID('tempdb..#PLUTotals') IS NOT NULL
DROP TABLE [dbo].[#PLUTotals];
IF OBJECT_ID('tempdb..#Level4Totals') IS NOT NULL
DROP TABLE [dbo].[#Level4Totals];
IF OBJECT_ID('tempdb..#WasteTotals') IS NOT NULL
DROP TABLE [dbo].[#WasteTotals];
IF OBJECT_ID('tempdb..#GPTotal') IS NOT NULL
DROP TABLE [dbo].[#GPTotal];
SELECT SiteNo
,CASE
WHEN Media = 1003
THEN isNull(Value,0)
ELSE 0
END AS GrossSalesValue
,CASE
WHEN Media = 1001
THEN isNull(Qty,0)
ELSE 0
END AS CustomerCount
,CASE
WHEN Media = 5001
AND MediaIndex IN(1,8)
THEN isNull(Value,0)
ELSE 0
END AS EodDiscount
,CASE
WHEN Media = 5006
THEN isNull(Value,0)
ELSE 0
END AS TotalDiscount
,CASE
WHEN Media = 5001
AND MediaIndex IN(26,168,35,38,72,193,119,208,211,212,213,47)
THEN isNull(Qty,0)
ELSE 0
END AS MealDeal
INTO #PLUTotals
FROM TransactionDetail
WHERE EXISTS
(
SELECT Descendant
FROM DescendantSites
WHERE Parent IN(@SiteNo)
AND Descendant = TransactionDetail.SiteNo
)
AND Date BETWEEN @SessionDateFrom AND @SessionDateTo
AND TermNo > 0
AND EXISTS
(
SELECT MediaNo
FROM Media
WHERE MediaNo IN(1001,1003,5001,5006)
AND MediaNo = TransactionDetail.Media
);
SELECT SiteNo
,isNull(SUM(Value),0) AS Level4Sales
INTO #Level4Totals
FROM PLUSalesExtended
WHERE [Level] + 1 = 4
AND SessionDate BETWEEN @SessionDateFrom AND @SessionDateTo
AND TermNo = 0
AND PluEntryType <> 4
AND EXISTS
(
SELECT Descendant
FROM DescendantSites
WHERE Parent IN(@SiteNo)
AND Descendant = PluSalesExtended.SiteNo
)
GROUP BY SiteNo;
SELECT CustomerCode AS SiteNo
,ABS(SUM(Waste_StandardCost)) AS WasteTotals
INTO #WasteTotals
FROM [CybakeRS].[dbo].utf_Waste(@SessionDateFrom,@SessionDateTo,1)
WHERE EXISTS
(
SELECT Descendant
FROM DescendantSites
WHERE Parent IN(@SiteNo)
AND Descendant = CybakeRS.dbo.utf_Waste.CustomerCode
)
AND Waste_Qty <> 0
GROUP BY CustomerCode;
SELECT PluSales.SiteNo
,SUM(PluSales.Value) AS SalesValue
,SUM(PluSales.VatValue) AS VatAmount
,SUM(PluSales.Cost) AS CostOfSales
INTO #GPTotal
FROM PluSales
INNER JOIN Sessions ON sessions.siteno = plusales.siteno
AND sessions.sessionno = plusales.sessionno
AND sessions.termno = plusales.termno
AND sessions.type = 7
AND sessions.termno = 0
WHERE sessions.sessiondate BETWEEN @SessionDateFrom AND @SessionDateTo
AND EXISTS
(
SELECT Descendant
FROM DescendantSites
WHERE Parent IN(@SiteNo)
AND Descendant = PluSales.SiteNo
)
GROUP BY PluSales.SiteNo;
SELECT CASE
WHEN PT.SiteNo IN(70,71)
THEN 69
WHEN PT.SiteNo IN(40,41)
THEN 45
ELSE PT.SiteNo
END AS SiteNo
,SUM(PT.GrossSalesValue) AS GrossSalesValue
,SUM(PT.CustomerCount) AS CustomerCount
,SUM(PT.EodDiscount) AS EodDiscount
,SUM(PT.TotalDiscount) AS TotalDiscount
,SUM(PT.MealDeal) AS MealDeal
,L4T.Level4Sales AS Level4Sales
,WT.WasteTotals AS WasteTotals
,SUM(PT.MealDeal) / SUM(PT.CustomerCount) AS MealDealPercent
,L4T.Level4Sales / (L4T.Level4Sales + SUM(PT.EodDiscount)) AS Conversion
,SUM(PT.GrossSalesValue) / SUM(PT.CustomerCount) AS AveSpend
,SUM(GPT.SalesValue) AS GPTSales
,SUM(GPT.CostOfSales) AS GPTCosts
,SUM(GPT.VatAmount) AS GPTVatAmount
FROM #PLUTotals AS PT
LEFT JOIN #Level4Totals AS L4T ON PT.SiteNo = L4T.SiteNo
LEFT JOIN #WasteTotals AS WT ON PT.SiteNo = WT.SiteNo
LEFT JOIN #GPTotal AS GPT ON PT.SiteNo = GPT.SiteNo
GROUP BY CASE
WHEN PT.SiteNo IN(70,71)
THEN 69
WHEN PT.SiteNo IN(40,41)
THEN 45
ELSE PT.SiteNo
END
,L4T.Level4Sales
,WT.WasteTotals;
DROP TABLE [dbo].[#PLUTotals];
DROP TABLE [dbo].[#Level4Totals];
DROP TABLE [dbo].[#WasteTotals];
DROP TABLE [dbo].[#GPTotal];
The code runs 5 queries. Query 1-4 prefetchs all the required data and stores the results into their own temp table. Query 5 then joins all the temp tables to give me the required result set. We have evidence that an index can improve the performance drastically but this cant easily be implemented in the clients solution at the moment. Any advice on how to improve this would be greatly appreciated. Are there any obvious pit falls that I am falling into?
DROP
at the top without anIF
, a copy/paste mistake? \$\endgroup\$