SUM() recursion issue when joining tables

Question

I have a performance issue with one of my queries.

The slow query:

SELECT
    Stock.StockID,
    Stock.sku AS SKU, 
    Stock.ProductName AS PRODUCT, 
    SUM(OrderItems.[quantity-purchased]) AS Qty 
FROM 
    Orders, OrderItems, CMRC_Stock as Stock
WHERE
    Orders.[status] = 'PRINTED' AND 
    Orders.[order-id] = OrderItems.[order-id] AND
    (Stock.SKU = OrderItems.SKU OR
    OrderItems.sku IN (SELECT SKU FROM AlternateSKUS WHERE StockID = Stock.StockID) OR
    Stock.BarCode = OrderItems.SKU) AND
    Orders.channelId != 21

GROUP BY Stock.StockID, Stock.sku, Stock.ProductName 
ORDER BY Qty DESC, Stock.sku

This takes around 11 seconds to return the result.

I tried to optimise the query, removing the nested SELECT from the WHERE clause and came up with this:

SELECT
    Stock.StockID,
    Stock.sku AS SKU, 
    Stock.ProductName AS PRODUCT, 
    SUM(OrderItems.[quantity-purchased]) AS Qty 
FROM
    Orders

FULL    OUTER JOIN OrderItems ON Orders.[order-id] = OrderItems.[order-id]
LEFT    OUTER JOIN CMRC_Stock as Stock ON OrderItems.sku = Stock.SKU
LEFT    OUTER JOIN AlternateSKUS ON AlternateSKUS.StockID = Stock.StockID

WHERE
    Orders.[status] = 'PRINTED' AND
    (Stock.SKU = OrderItems.SKU OR 
        AlternateSKUS.SKU = OrderItems.sku OR
        Stock.BarCode = OrderItems.SKU) AND
    Orders.channelId != 21

GROUP BY Stock.StockID, Stock.sku, Stock.ProductName 
ORDER BY Qty DESC, Stock.sku

It runs much faster < 1 second.

There is a problem however. It appears that I have a recursion issue with my SUM() function in that it's multiplying the correct quantity, by how many "AlternateSKUs" records that exist for the same StockID.

For example, there if there is 1 Order, for 1 OrderItem, then it is being counted (QTY) as 4, because there are 4 AlternateSKUs for it. If a quantity of 2 was purchased for the same item, then QTY would return 8. Same for if there are other Orders for the same OrderItem, the number of Items is multiplied by the number of AlternateSKU records there are for it. E.G 3 seperate OrderItems belong to seperate Orders would yeild a QTY of 12 for the same item.

I realise that this is ultimately down to a poorly designed schema, however I have to work with what I've got.

How can I fix this?

always use explicit joins (table1 INNER JOIN table2 ON) when writing queries. using "table1,table2,table3" syntax is an invitation for cartesian product errors. — Jeremy, Jun 23 '15 at 15:25
@Jeremy, Please could you elaborate? My SQL experience is rather basic. — Dan, Jun 23 '15 at 15:27
see http://www.w3schools.com/sql/sql_join.asp and http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause — Jeremy, Jun 23 '15 at 15:33
[Here](http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax) is a good explanation of the JOIN syntax issue which @Jeremy was referring to. — Bacon Bits, Jun 23 '15 at 15:33
Having refactored the query with the above recommendation, still facing the same issue. — Dan, Jun 24 '15 at 09:39
Updated, please see the revised secondary query, along with further explanaton of the issue below it — Dan, Jun 24 '15 at 10:53
@jpw Seems we're getting closer. Now it appears it's not including results where the OrderItems.SKU is an AlternateSKU. — Dan, Jun 24 '15 at 11:21

score 1 · Accepted Answer · answered Jun 24 '15 at 11:41

It all boils down to the relationship between [CMRC_Stock] and [OrderItems]. It is possible to have a stock SKU with multiple alternative SKUs. Each order item's quantity would thence be multiplied by the number of alternative SKUs.

What I have done below is refactor your original query such that this retrieves a distinct list of SKUs/Stock IDs first, from which it can then join [OrderItems] to [CMRC_Stock]:

WITH StockSKUs([StockID],[SKU]) AS (
    SELECT 
        [StockID],[SKU]
    FROM [CMRC_Stock]
    UNION
    SELECT
        [StockID],[BarCode]
    FROM [CMRC_Stock]
    UNION
    SELECT
        [StockID],[SKU]
    FROM [AlternateSKUs]
    )
SELECT
    Orders.[order-id],
    Stock.StockID,
    Stock.sku AS SKU, 
    Stock.ProductName AS PRODUCT,
    SUM(OrderItems.[quantity-purchased]) as Qty

FROM 
    Orders
JOIN OrderItems
    ON Orders.[order-id] = OrderItems.[order-id]
JOIN StockSKUs
    ON OrderItems.[SKU] = StockSKUs.[SKU]
JOIN CMRC_Stock as Stock 
    ON StockSKUs.[StockID] = Stock.[StockID]
WHERE
    Orders.[status] = 'PRINTED'
    AND Orders.channelId != 21

GROUP BY Orders.[order-id], Stock.StockID, Stock.SKU, Stock.ProductName

ORDER BY Qty DESC, Stock.SKU

A SQL Fiddle will follow.

Nailed it. Thanks ever so much, and thanks for the explanation. I've learned something new today. Didn't even know about the WITH statement. — Dan, Jun 24 '15 at 11:54

SUM() recursion issue when joining tables

1 Answers1