Help improving performance of a query

  • Lisa Cherney

    SSC Veteran

    Points: 207

    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

  • Lynn Pettis

    SSC Guru

    Points: 442138

    One question, is zero (0) a valid value in the temporary table #BaseStoreList?

  • Lisa Cherney

    SSC Veteran

    Points: 207

    Lynn Pettis - Thursday, July 12, 2018 12:44 PM

    One question, is zero (0) a valid value in the temporary table #BaseStoreList?

    Yes it is.

  • Luis Cazares

    SSC Guru

    Points: 183543

    Why are you grouping by VendorPrefix?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Lisa Cherney

    SSC Veteran

    Points: 207

    Luis Cazares - Thursday, July 12, 2018 1:57 PM

    Why 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).

  • Luis Cazares

    SSC Guru

    Points: 183543

    Lisa Cherney - Thursday, July 12, 2018 2:29 PM

    Luis Cazares - Thursday, July 12, 2018 1:57 PM

    Why 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Lisa Cherney

    SSC Veteran

    Points: 207

    Luis Cazares - Thursday, July 12, 2018 3:10 PM

    Lisa Cherney - Thursday, July 12, 2018 2:29 PM

    Luis Cazares - Thursday, July 12, 2018 1:57 PM

    Why 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.

  • Chris Harshman

    SSC-Forever

    Points: 41722

    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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply