SQL - improve NOT EXISTS query performance

Question

Is there a way I can improve this kind of SQL query performance:

INSERT
INTO ...
WHERE NOT EXISTS(Validation...)

The problem is when I have many data in my table (like million of rows), the execution of the WHERE NOT EXISTS clause if very slow. I have to do this verification because I can't insert duplicated data.

I use SQLServer 2005

thx

Answer 1

Make sure you are searching on indexed columns, with no manipulation of the data within those columns (like substring etc.)

Answer 2

Off the top of my head, you could try something like:

 TRUNCATE temptable
 INSERT INTO temptable ...
 INSERT INTO temptable ... 
 ...
 INSERT INTO realtable
 SELECT temptable.* FROM temptable
 LEFT JOIN realtable on realtable.key = temptable.key
 WHERE realtable.key is null

Answer 3

Try to replace the NOT EXISTS with a left outer join, it sometimes performs better in large data sets.