July 12, 2018 at 12:04 pm
The following query takes somewhere between 30 to 35 seconds to complete. Can something be done to speed it up? Actual Execution plan is attached. Any help/suggestions would be greatly appreciated.
with LineItemQtys as (select o.OrderID, case when ReturnStatus = 'S' then OrderItemID else OrigItemID end OrderItemID, VendorPrefix,
sum(case when ReturnStatus = 'O' or ReturnStatus = 'S' then Quantity else 0 end) OrderedQty,
sum(case when ReturnStatus = 'C' then Quantity * -1 else 0 end) CancelledQty,
sum(case when ReturnStatus = 'R' then Quantity * -1 else 0 end) ReturnedQty
from [Order] o
join OrderStatus os on o.OrderID = os.OrderID
join #MerchantList ml on os.MerchantID = ml.MerchantID
join #BaseStoreList sl on isnull(os.ShippedToBaseStore,0) = sl.BaseStoreID
join OrderLineItem oli on oli.OrderID = o.OrderID
where o.SystemOfRecordID <> 'AX' and os.Cancelled = 0 and os.Closed = 0
group by o.OrderID, case when ReturnStatus = 'S' then OrderItemID else OrigItemID end, VendorPrefix),
OrderShipmentQtys as (select OrderID, OrderItemID, sum(Quantity) InvoicedQty
from OrderShipmentItem
group by OrderID, OrderItemID)
select distinct li.OrderID
from LineItemQtys li
join OrderShipmentQtys osq on li.OrderID = osq.OrderID and li.OrderItemID = osq.OrderItemID
where OrderedQty - CancelledQty - ReturnedQty < InvoicedQty
DML for tables involved:
CREATE TABLE [dbo].[Order](
[OrderID] [char](20) NOT NULL,
[MerchantID] [char](8) NOT NULL,
[OrderDate] [smalldatetime] NOT NULL,
[PaymentMethodID] [tinyint] NULL,
[CustomerID] [char](14) NULL,
[SpecialInstructions] [ntext] NULL,
[MerchantOrderID] [varchar](50) NULL,
[ShippingMethod] [varchar](50) NULL,
[TaxArea] [varchar](50) NULL,
[InitialPaymentMethodID] [tinyint] NULL,
[ApproverID] [int] NULL,
[OrderedFromIP] [varchar](16) NULL,
[GatewayAccountID] [int] NULL,
[SkinID] [int] NULL,
[ExtrnSystem] [varchar](10) NULL,
[RequestedDeliveryDate] [datetime] NULL,
[CustReferenceNo] [varchar](50) NULL,
[CustCategoryID] [int] NULL,
[CustCategoryGroupID] [int] NULL,
[ConvertedCartID] [int] NULL,
[SystemOfRecordID] [varchar](6) NOT NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ([OrderID] ASC)
)
GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180711-142852] ON [dbo].[Order]
(
[SystemOfRecordID] ASC
)
GO
CREATE TABLE [dbo].[OrderStatus](
[OrderID] [char](20) NOT NULL,
[MerchantID] [char](8) NOT NULL,
[TotalOrderShipped] [bit] NOT NULL,
[ShipmentStatusID] [tinyint] NOT NULL,
[PaymentStatusID] [tinyint] NOT NULL,
[OLPaymentProcessStatus] [int] NULL,
[Cancelled] [bit] NOT NULL,
[Closed] [bit] NOT NULL,
[AVSCode] [varchar](32) NULL,
[ShippedToBaseStore] [int] NULL,
[ShippedToCustomer] [bit] NOT NULL,
[POExported] [bit] NOT NULL,
[InvoicingStatus] [int] NOT NULL,
[DeliveryStatus] [int] NOT NULL,
[SettlementStatus] [int] NOT NULL,
CONSTRAINT [PK_OrderTracking] PRIMARY KEY CLUSTERED ([OrderID] ASC)
)
GO
CREATE NONCLUSTERED INDEX [IX_OrderStatusShippedToStore] ON [dbo].[OrderStatus]
(
[ShippedToBaseStore] ASC,
[MerchantID] ASC
)
GO
CREATE TABLE [dbo].[OrderLineItem](
[OrderItemID] [int] NOT NULL,
[OrderID] [char](20) NOT NULL,
[ProductID] [varchar](50) NOT NULL,
[ProductIDModifier] [varchar](50) NULL,
[Quantity] [int] NOT NULL,
[Price] [money] NOT NULL,
[Options] [varchar](1024) NULL,
[Description] [varchar](512) NULL,
[Tax] [money] NOT NULL,
[VendorPrefix] [varchar](4) NULL,
[MeasureUnit] [varchar](20) NULL,
[Cost] [money] NULL,
[Intercompany] [bit] NOT NULL,
[OrigItemID] [int] NULL,
[ReturnStatus] [char](1) NOT NULL,
[JWOD] [bit] NOT NULL,
[Recycled] [bit] NOT NULL,
[ModDate] [datetime] NULL,
[MSRP] [money] NULL,
[ModBy] [varchar](50) NULL,
[TAACompliant] [bit] NULL,
[CatalogID] [int] NULL,
[UNSPSC] [varchar](50) NULL,
[SmallBusiness] [bit] NULL,
[UPC] [varchar](14) NULL,
[SalesCategory] [char](3) NULL,
[DropShip] [bit] NULL,
[AmazonASIN] [varchar](50) NULL,
[AmazonListingIdentifier] [varchar](200) NULL,
CONSTRAINT [PK_OrderLineItem] PRIMARY KEY CLUSTERED ([OrderID] ASC,[OrderItemID] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX_OrderLineItem_OrderID] ON [dbo].[OrderLineItem]
(
[OrderID] ASC
)
GO
CREATE NONCLUSTERED INDEX [OrderLineItem10] ON [dbo].[OrderLineItem]
(
[OrderItemID] ASC,
[OrderID] ASC,
[Quantity] ASC,
[OrigItemID] ASC,
[ReturnStatus] ASC
)
GO
CREATE NONCLUSTERED INDEX [OrderLineItem8] ON [dbo].[OrderLineItem]
(
[OrderID] ASC,
[VendorPrefix] ASC,
[OrigItemID] ASC,
[ReturnStatus] ASC,
[OrderItemID] ASC,
[Quantity] ASC
)
GO
CREATE NONCLUSTERED INDEX [OrderLineItem9] ON [dbo].[OrderLineItem]
(
[OrderItemID] ASC,
[OrderID] ASC,
[VendorPrefix] ASC
)
GO
create table #MerchantList (MerchantID char(8) collate Latin1_General_CS_AS primary key);
create table #BaseStoreList (BaseStoreID int primary key);
GO
July 12, 2018 at 12:44 pm
One question, is zero (0) a valid value in the temporary table #BaseStoreList?
July 12, 2018 at 1:22 pm
Lynn Pettis - Thursday, July 12, 2018 12:44 PMOne question, is zero (0) a valid value in the temporary table #BaseStoreList?
Yes it is.
July 12, 2018 at 1:57 pm
July 12, 2018 at 2:29 pm
Luis Cazares - Thursday, July 12, 2018 1:57 PMWhy are you grouping by VendorPrefix?
It's one of the columns I need included (you may have missed it, you have to scroll to see it in the first line of my query).
July 12, 2018 at 3:10 pm
Lisa Cherney - Thursday, July 12, 2018 2:29 PMLuis Cazares - Thursday, July 12, 2018 1:57 PMWhy are you grouping by VendorPrefix?It's one of the columns I need included (you may have missed it, you have to scroll to see it in the first line of my query).
You're only using it in the CTE declaration, but not anywhere else.
July 13, 2018 at 6:07 am
Luis Cazares - Thursday, July 12, 2018 3:10 PMLisa Cherney - Thursday, July 12, 2018 2:29 PMLuis Cazares - Thursday, July 12, 2018 1:57 PMWhy are you grouping by VendorPrefix?It's one of the columns I need included (you may have missed it, you have to scroll to see it in the first line of my query).
You're only using it in the CTE declaration, but not anywhere else.
Ah - I see now that yes, it truly is unused. But removing it from the query didn't have any impact on the performance.
July 13, 2018 at 10:48 am
The biggest problems offhand are:
- dealing with the 2 different columns to group OrderLineItem by depending on the ReturnStatus, either OrderItemID or OrigItemID. There's a Hash Match (Aggregate) operation consuming large amounts of CPU.
- dealing with the fact that the main filtering condition cannot be evaluated until after all the computing of the 3 quantities and aggregation are complete
- doing the GROUP BY for LineItemQtys after all the joins, when really it seems like only OrderLineItem rows are the ones that need aggregated.
Is there some way to simplify the grouping of OrderLineItem rows so it doesn't have to evaluate both OrderItemID and OrigItemID?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply