Refactoring SQL to be scalable with a faster execution time

Viewed 53

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:

enter image description here

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'

enter image description here

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;

enter image description here

0 Answers
Related