SQL Server 2005: Delete Optimization

Is this the most efficient way to delete from DeletedProducts table where there are not references found in the ProductFileInfo table?

Sample:

DELETE FROM DeletedProducts 
WHERE ProductId NOT IN SELECT DISTINCT ProductID FROM ProductFileInfo

Or is NOT EXIST a better way to perform 开发者_开发百科this.

Note: ProductFileInfo has over 20 Million records in it.

SQL Server 2005 Standard is what I am using.

Thanks

NOT IN and NOT EXISTS will probably produce the same plan. How many rows are you going to delete? If it is a lot I would do batches of 5K or 10K this way you won't fill your LOG with one big transaction and then if it fails for whatever reason it needs to do a big rollback

for example

DELETE top 5000
from sometable
where ....
go 100 --will be executed 100 times

in order for GO N to work you need SSMS and service pack 2 (IIRC) but of course you can also write a while loop..while @@rowcount > 0.......

try multiple solutions and test their performance, YMMV. Also try an outer join

DELETE FROM DeletedProducts d left outer join ProductFileInfo p
on d.ProductId  = p.ProductId  WHERE p.ProductID is null

Well, I typically write something along the lines of

delete d
from TableToDeleteFrom d
where not exists(select top 1 1 from SomeOtherTable sot where sot.ThatId = d.ThatId)

Probably, you don't want to lock the look-up table, so you can specify with(readpast) hint or with(nolock).

This is a great resource for exactly what your asking.

http://www.sql-server-performance.com/tips/t_sql_where_p3.aspx