NOT EXISTS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

NOT EXISTS

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)
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
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?"
brad, royv: thanks for your prompt help, it fixed my problem…
gee sounds familiar…where did I see that again?
]]>