Performance enhancement for NOT IN Insert

Question

I have the following Insert in an MS SQL Stored Procedure:

insert into Status_Table(Field1, Field2, Field3, Status_ID, Status_ID2)
select a.Field1 , a.Field2 , a.Field3 , a.Status_ID , a.Status_ID2 
  from ##StatusTempTable as a  
  where a.Status_ID+a.Status_ID2 not in(select Status_ID+Status_ID2 from Status_Table)

Before i do the Insert, i will check if there is any row exist with the same Status_ID and Status_ID2. Table Status_Table contains over 3 Mio rows, the Insert is getting very long. Are there alternatives to increase the Peroformance ?

Answer 1

Try to use NOT EXISTS instead of NOT IN

insert into Status_Table(Field1, Field2, Field3, Status_ID, Status_ID2)
select a.Field1 , a.Field2 , a.Field3 , a.Status_ID , a.Status_ID2 
  from ##StatusTempTable as a  
  where not exists(select * from Status_Table s where s.Status_ID = a.Status_ID AND s.Status_ID2 = s.Status_ID2 )