What could I do to further reduce the execution time of my query?

Viewed 48

I am trying to get the execution time as low as possible for the following query, however I am not that experienced in optimization. I have looked into different options and I believe that indexing would be beneficial, but I am not sure where I would use indexes nor how to implement them.

Here is my query:

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(sli.UnitsSold) as TotalItems,
    round(case 
        when sum(sli.SalePrice) = 0 then 0 
        else sum(sli.SalePrice - (pc.ManufacturingPrice * sli.UnitsSold)) / sum(sli.SalePrice) 
    end, 2) as Margin,
    round(sum((pc.RRP * sli.UnitsSold) - sli.SalePrice) / sum(pc.RRP * sli.UnitsSold), 2) 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

The only indexes I currently have are clustered indexes on the primary key of each of the three tables:

  • SalesOrder = SalesOrderID
  • SalesOrderLineItem = SalesOrderLineItemID
  • ProductCost = ProductCostID

The create table for each of the tables are as follows:

CREATE TABLE [dbo].[ProductCost](
    [ProductCostID] [smallint] IDENTITY(1,1) NOT NULL,
    [ProductID] [tinyint] NULL,
    [CountryID] [tinyint] NULL,
    [ManufacturingPrice] [float] NULL,
    [RRP] [float] NULL,
PRIMARY KEY CLUSTERED 
(
    [ProductCostID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ProductCost]  WITH CHECK ADD FOREIGN KEY([CountryID])
REFERENCES [dbo].[Country] ([CountryID])
GO

ALTER TABLE [dbo].[ProductCost]  WITH CHECK ADD FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([ProductID])
GO


USE [FinanceDB]
GO


CREATE TABLE [dbo].[SalesOrder](
    [SalesOrderID] [bigint] IDENTITY(1,1) NOT NULL,
    [SalesOrderNumber] [nvarchar](24) NULL,
    [SalesOrderDate] [datetime] NULL,
    [SalesPersonID] [tinyint] NULL,
    [SalesRegionID] [smallint] NULL,
    [SalesMonth] [date] NULL,
PRIMARY KEY CLUSTERED 
(
    [SalesOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SalesOrder]  WITH CHECK ADD FOREIGN KEY([SalesPersonID])
REFERENCES [dbo].[SalesPerson] ([SalesPersonID])
GO

ALTER TABLE [dbo].[SalesOrder]  WITH CHECK ADD FOREIGN KEY([SalesRegionID])
REFERENCES [dbo].[SalesRegion] ([SalesRegionID])
GO

USE [FinanceDB]
GO


CREATE TABLE [dbo].[SalesOrderLineItem](
    [SalesOrderLineItemID] [bigint] IDENTITY(1,1) NOT NULL,
    [SalesOrderID] [bigint] NULL,
    [SalesOrderLineNumber] [smallint] NULL,
    [PromotionID] [smallint] NULL,
    [ProductID] [tinyint] NULL,
    [UnitsSold] [smallint] NULL,
    [SalePrice] [float] NULL,
 CONSTRAINT [PK__SalesOrd__DAA33720861CAF46] PRIMARY KEY NONCLUSTERED 
(
    [SalesOrderLineItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SalesOrderLineItem]  WITH CHECK ADD FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([ProductID])
GO

ALTER TABLE [dbo].[SalesOrderLineItem]  WITH CHECK ADD FOREIGN KEY([PromotionID])
REFERENCES [dbo].[Promotion] ([PromotionID])
GO

ALTER TABLE [dbo].[SalesOrderLineItem]  WITH CHECK ADD FOREIGN KEY([SalesOrderID])
REFERENCES [dbo].[SalesOrder] ([SalesOrderID])
GO

My query execution plan is as follows: https://www.brentozar.com/pastetheplan/?id=S1FnBppgj

And here is some dummy data

SalesOrderLineItem

SalesOrderLineItemID SalesOrderID SalesOrderLineItem PromotionID ProductID UnitsSold SalePrice
1 1 1 8 2 2 19.6

ProductCost

ProductCostID ProductID CountryID ManufaturingPrice RRP
1 2 1 3 3

SalesOrder

SalesOrderID SalesOrderNumber SalesOrderDate SalesPersonID SalesRegionID SalesMonth
1 SP201079411A 2010-09-26 00:00:00.000 33 49 2010-09-01
0 Answers
Related