Tommy Graffam Tommy Graffam - 6 months ago 13
SQL Question

3 way join with subqueries and multiple where clause

I am trying to solve a problem where when i run my query it just runs forever and doesn't execute when i have 3 where clauses, but if I only have 2 where clauses it executes totally fine.

SELECT top(1)
INV.SUBINVENTORY_CODE, INV.LOCATOR_CODE, INV.ITEM_CODE, dp.DESCRIPTION, dp.barcode1, dp.barcode2, INV.QTY,
dp.FROM_SUB_INVENTORY_CODE, dp.FROM_LOCATOR_CODE, INV.SUBINVENTORY_CODE, Inv.INVENTORY_ITEM_ID, dp_hist.barcode1,
dp_hist.barcode2, dp_hist.DESCRIPTION,dp_hist.FROM_SUB_INVENTORY_CODE, dp_hist.FROM_LOCATOR_CODE
FROM
INVENTORY AS INV FULL OUTER JOIN deliveries_picks_hist as dp_hist ON Inv.item_code = dp_hist.item_code
FULL OUTER JOIN
deliveries_picks AS dp ON dp_hist.item_code = dp.ITEM_CODE
WHERE
INV.LOCATOR_CODE = 'BMS.S.T.G' and
(inv.ITEM_CODE in
((select TOP(1) ITEM_CODE from deliveries_picks_hist where BARCODE1 = '" & barcode & "' or BARCODE2 = '" & barcode & "' or inv.ITEM_CODE = 'ASM.EN.CD'),
(select TOP(1) ITEM_CODE from deliveries_picks where BARCODE1 = '" & barcode & "' or BARCODE2 = '" & barcode & "' or inv.ITEM_CODE = 'ASM.EN.CD')))
group by
INV.INVENTORY_ITEM_ID, INV.LOCATOR_CODE, INV.ITEM_CODE, INV.QTY, dp_hist.barcode1, dp_hist.barcode2, dp.DESCRIPTION,
dp.FROM_LOCATOR_CODE, dp.FROM_SUB_INVENTORY_CODE, INV.SUBINVENTORY_CODE, DP.BARCODE1, DP.BARCODE2, dp_hist.DESCRIPTION,
dp_hist.FROM_SUB_INVENTORY_CODE, dp_hist.FROM_LOCATOR_CODE
order by
dp_hist.DESCRIPTION


when I add
inv.ITEM_CODE = 'ASM.EN.CD'
then the query doesn't work but if I were to switch out one of the inner joins and keep the
inv.ITEM_CODE = 'ASM.EN.CD'
then it also works fine, I have not been able to pin point the problem, i have reformatted and tried a few different ways to work it but not luck.

Answer

I believe that what you wanted was what I have below. You're wanting to keep INV rows where ITEM_CODE is equal to ASM.EN.CD regardless of the deliveries_picks* lookup.

ORs are generally going to slow down a query. But I think the reason for the slow-down here is most likely because including the extra condition in the subqueries caused a lot more rows to match since it was correlated on an outer table (INV). In other words, all the rows in the subquery tables are going to pass the filter when that extra condition is true. The optimizer might not have made a plan that leverages the fact you only wanted to look at the "first" one.

Note that you do potentially have a problem when using TOP without an ORDER BY in those subqueries. There really isn't a first row without one. And by the way, your use of full joins seems suspicious to me as do all the grouping columns. If you can describe the table relationships it's possible you'll get a much better query. Finally those "barcode" conditions might be simpler as @barcode in (BARCODE1, BARCODE2) so you don't have to repeat the concatenation.

SELECT TOP 1 
    INV.SUBINVENTORY_CODE, INV.LOCATOR_CODE, INV.ITEM_CODE,
    dp.DESCRIPTION, dp.barcode1, dp.barcode2, INV.QTY,  
    dp.FROM_SUB_INVENTORY_CODE, dp.FROM_LOCATOR_CODE,
    INV.SUBINVENTORY_CODE, Inv.INVENTORY_ITEM_ID, dp_hist.barcode1, 
    dp_hist.barcode2, dp_hist.DESCRIPTION,
    dp_hist.FROM_SUB_INVENTORY_CODE, dp_hist.FROM_LOCATOR_CODE 
FROM 
    INVENTORY AS INV
    FULL OUTER JOIN deliveries_picks_hist AS dp_hist ON Inv.item_code = dp_hist.item_code 
    FULL OUTER JOIN deliveries_picks AS dp ON dp_hist.item_code = dp.ITEM_CODE 
WHERE 
    INV.LOCATOR_CODE = 'BMS.S.T.G'
    AND INV.ITEM_CODE in (
        (
            select TOP 1 ITEM_CODE from deliveries_picks_hist
            where BARCODE1 = '" & barcode & "' or BARCODE2 = '" & barcode & "'
        ),    
        (
            select TOP 1 ITEM_CODE from deliveries_picks
            where BARCODE1 = '" & barcode & "' or BARCODE2 = '" & barcode & "'
        ),
        'ASM.EN.CD'
    )
group by 
    INV.INVENTORY_ITEM_ID, INV.LOCATOR_CODE, INV.ITEM_CODE, INV.QTY,
    dp_hist.barcode1, dp_hist.barcode2, dp.DESCRIPTION, 
    dp.FROM_LOCATOR_CODE,  dp.FROM_SUB_INVENTORY_CODE,
    INV.SUBINVENTORY_CODE, DP.BARCODE1, DP.BARCODE2, dp_hist.DESCRIPTION,
    dp_hist.FROM_SUB_INVENTORY_CODE, dp_hist.FROM_LOCATOR_CODE 
order by 
    dp_hist.DESCRIPTION

Using CASE should work to "defer" execution of the subqueries if that doesn't work:

    AND
    CASE
        WHEN INV.ITEM_CODE = 'ASM.EN.CD' THEN 1
        WHEN INV.ITEM_CODE IN (
            (
                select TOP 1 ITEM_CODE from deliveries_picks_hist
                where BARCODE1 = '" & barcode & "' or BARCODE2 = '" & barcode & "'
            ),    
            (
                select TOP 1 ITEM_CODE from deliveries_picks
                where BARCODE1 = '" & barcode & "' or BARCODE2 = '" & barcode & "'
            )
        ) THEN 1
    END = 1

My guess is that this is your intention on the join logic:

FROM 
    INVENTORY AS INV LEFT OUTER JOIN (
        deliveries_picks AS dp
        FULL OUTER JOIN
        deliveries_picks_hist AS dp_hist
            ON dp_hist.ITEM_CODE = dp.ITEM_CODE
    )
        ON INV.ITEM_CODE = COALESCE(dp.ITEM_CODE, dp_hist.ITEM_CODE)

Normally when you see a full join you'll also see a bunch of COALESCE() operations.