SQL Server Performance

NOT EXISTS

Discussion in 'T-SQL Performance Tuning for Developers' started by sqljunkie, Jan 7, 2003.

  1. sqljunkie New Member

    I would like to figure out a way to not use the 'NOT EXISTS' portion of the following query as it is causing a Nested Loops join. As the query executes it runs slower and slower because there is more data to compare in the Nested Loops join. Basically I want to insert something from Table1 that doesn't already exist in table 2. Would anyone know of another way to write it:

    Insert into MyTable2
    select top 1 userID
    from MyTable1 a where NOT EXISTS
    (select * from MyTable2 b where a.userID = b.userID)



  2. bradmcgehee New Member

    Are you saying that (select * from MyTable2 b where a.userID = b.userID), when run by itself, produces a loop join, but when run as part of the NOT EXISTS code, uses a nested join? If so, one option would be use a join hint to tell the query to perform a loop join instead of a nested join.

    Another option, although it may not be faster (you will have to test to see) is to use a left outer join instead of the NOT EXISTS code.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. royv New Member

    Insert into MyTable2
    select top 1 a.userID
    from MyTable1 a LEFT JOIN
    (select userID from MyTable2) b
    ON a.UserID=b.UserID
    WHERE b.userID IS NULL


    "How do you expect to beat me when I am forever?"
  4. sqljunkie New Member

    brad, royv: thanks for your prompt help, it fixed my problem...
  5. x002548 New Member

    gee sounds familiar...where did I see that again?

Share This Page