Post reply

Compare field to results from another table

  • June 23, 2015 at 4:57 pm

    #318497

    I have a stored procedure that runs and it returns all items in our system that are flagged with one of two ID #. Running that on its own works perfectly takes about 1 second to run. right now it only returns one result.

    The problem comes when I need to compare it to another table to make sure we don't insert a duplicate in the other table.

    I have the table to check on a left outer join and in my where clause I have this not in

    DB1.dbo.ITEM.item_ItemNumber not in (select DB2.dbo.tblFPO.WarrantyWOID from DB2.dbo.tblFPO where DIV_ID <> 5 and DIV_ID is Not null and WarrantyWOID is Not null)

    So what I am doing it comparing the Item.ITEM_Itemnumber to see if it already exists in DB2.

    If I run the select in its own query window it returns about 19,000 rows in about 5 seconds.

    This takes over 16 min to run and it still wasn't done so I stopped it. If I take off all the parameters other then the <> 5 it runs in about 10 seconds but returns no results. It should be returning a result as I know this job is not in the second table. What am I missing here?

    Thanks

  • June 23, 2015 at 6:42 pm

    #1807600

    For starters, there is likely no need for the OUTER join if you're using WHERE NOT IN.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 24, 2015 at 7:31 am

    #1807733

    Thanks Jeff I have tried both an inner and a left. The left seems to speed it up a bit.

  • June 24, 2015 at 7:43 am

    #1807742

    DaveK2014 (6/24/2015)

    Thanks Jeff I have tried both an inner and a left. The left seems to speed it up a bit.

    Like I said previously, because of the WHERE NOT IN, you don't need either.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 24, 2015 at 12:00 pm

    #1807856

    Can you post the entire query?

    I'd suggest you try this pattern:

    WHERE NOT EXISTS(select DB2.dbo.tblFPO.WarrantyWOID from DB2.dbo.tblFPO where DIV_ID <> 5 and DIV_ID is Not null and WarrantyWOID = DB1.dbo.ITEM.item_ItemNumber)

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 5 posts - 1 through 4 (of 4 total)

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