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 |