I have an SQL snippet that is somewhat complex. Currently, it is taking roughly 7 seconds to execute. What I need to do is simplify and refactor this code so that it is more efficient and scalable. So far I have tried converting each select statement into CTEs then joining them afterwards, however this returned different results to the original. Although, this would increase the overall amount of code, it would likely make it easier to add more features later.
I have considered adding indexes as this would likely improve the execution time/plan however I am not sure what columns I would need to add these indexes on.
Results from original query:
Below is the code snipped:
select
basic_metrics.SalesOrderDate,
basic_metrics.SalesOrderNumber,
basic_metrics.SalesPersonID,
margin_calculation.SalesOrderID,
basic_metrics.TotalSalesPrice,
basic_metrics.TotalCost,
basic_metrics.TotalRRP,
basic_metrics.UniqueItems,
basic_metrics.TotalItems,
round(margin_calculation.Margin, 2) as Margin,
round(discount_calculation.PercentageDiscount, 2) as PercentageDiscount
from (
-- Calculate Discount
select
so.SalesOrderID,
sum((pc.RRP * sli.UnitsSold) - SalePrice) / sum(pc.RRP * sli.UnitsSold) as PercentageDiscount
from SalesOrder so
inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID
inner join ProductCost pc on pc.ProductID = sli.ProductID
group by
so.SalesOrderID
) discount_calculation
inner join (
-- Calculate Margin
select
so.SalesOrderID,
case
when sum(SalePrice) = 0 then 0
else sum(SalePrice - (pc.ManufacturingPrice * sli.UnitsSold)) / sum(SalePrice)
end as Margin
from SalesOrder so
inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID
inner join ProductCost pc on pc.ProductID = sli.ProductID
group by
so.SalesOrderID
) margin_calculation on margin_calculation.SalesOrderID = discount_calculation.SalesOrderID
inner join (
-- basic metrics
select
so.SalesOrderID,
so.SalesOrderNumber,
so.SalesOrderDate,
so.SalesPersonID,
so.SalesMonth,
sum(sli.SalePrice) as TotalSalesPrice,
sum(pc.ManufacturingPrice * sli.UnitsSold) as TotalCost,
sum(pc.RRP * sli.UnitsSold) as TotalRRP,
count(distinct sli.ProductID) as UniqueItems,
sum(UnitsSold) as TotalItems
from SalesOrder so
inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID
inner join ProductCost pc on pc.ProductID = sli.ProductID
group by
so.SalesOrderID,
so.SalesOrderNumber,
so.SalesOrderDate,
so.SalesPersonID,
so.SalesMonth
) basic_metrics on basic_metrics.SalesOrderID = margin_calculation.SalesOrderID
where SalesOrderDate > '2016-01-01'
and here is my execution plan: https://www.brentozar.com/pastetheplan/?id=ryAsHZigi
What can I do to simplify this query, while still returning the same results and improve the execution times?
Edit:
CTE Attempt:
with cte1 as -- Calculate Discount
(
select
so.SalesOrderID,
sum((pc.RRP * sli.UnitsSold) - SalePrice) / sum(pc.RRP * sli.UnitsSold) as PercentageDiscount
from
SalesOrder so
inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID
inner join ProductCost pc on pc.ProductID = sli.ProductID
group by
so.SalesOrderID
),
cte2 as -- Calculate Margin
(
select
so.SalesOrderID,
case
when sum(SalePrice) = 0 then 0
else sum(
SalePrice - (pc.ManufacturingPrice * sli.UnitsSold)
) / sum(SalePrice)
end as Margin
from
SalesOrder so
inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID
inner join ProductCost pc on pc.ProductID = sli.ProductID
group by
so.SalesOrderID
),
cte3 as -- basic metrics
(
select
so.SalesOrderID,
so.SalesOrderNumber,
so.SalesOrderDate,
so.SalesPersonID,
so.SalesMonth,
sum(sli.SalePrice) as TotalSalesPrice,
sum(pc.ManufacturingPrice * sli.UnitsSold) as TotalCost,
sum(pc.RRP * sli.UnitsSold) as TotalRRP,
count(distinct sli.ProductID) as UniqueItems,
sum(UnitsSold) as TotalItems
from
SalesOrder so
inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID
inner join ProductCost pc on pc.ProductID = sli.ProductID
group by
so.SalesOrderID,
so.SalesOrderNumber,
so.SalesOrderDate,
so.SalesPersonID,
so.SalesMonth
)
select
cte3.SalesOrderDate,
cte3.SalesOrderNumber,
cte3.SalesPersonID,
cte2.SalesOrderID,
cte3.TotalSalesPrice,
cte3.TotalCost,
cte3.TotalRRP,
cte3.UniqueItems,
cte3.TotalItems,
round(cte2.Margin, 2) as Margin,
round(cte1.PercentageDiscount, 2) as PercentageDiscount
from
cte1
inner join cte2 on cte2.SalesOrderID = cte1.SalesOrderID
inner join cte3 on cte3.SalesOrderID = cte2.SalesOrderID
where
SalesOrderDate > '2016-01-01'
EDIT 2:
I have also attempted to do it all in one query, however the results are even further off:
select
so.SalesOrderDate,
SO.SalesOrderNumber,
so.SalesPersonID,
so.SalesOrderID,
sum(sli.SalePrice) as TotalSalesPrice,
sum(pc.ManufacturingPrice * sli.UnitsSold) as TotalCost,
sum(pc.RRP * sli.UnitsSold) as TotalRRP,
count(distinct sli.ProductID) as UniqueItems,
sum(UnitsSold) as TotalItems,
case
when sum(sli.SalePrice) = 0 then 0
else sum(
sli.SalePrice - (pc.ManufacturingPrice * sli.UnitsSold)
) / sum(sli.SalePrice)
end as Margin,
sum((pc.RRP * sli.UnitsSold) - sli.SalePrice) / sum(pc.RRP * sli.UnitsSold) as PercentageDiscount
from
SalesOrder so
inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID
inner join ProductCost pc on pc.ProductID = sli.ProductID
where
SO.SalesOrderDate > '2016-01-01'
group by
so.SalesOrderID,
so.SalesOrderNumber,
so.SalesOrderDate,
so.SalesPersonID,
so.SalesMonth;


