CODEWITHSUNDEEP
sqlsql-serversql-server-2005query-optimization
RPS
RPS

Reputation: 1401

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

Upvotes: 2

Views: 535

Answers (4)

Denis Valeev
Denis Valeev

Reputation: 6015

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).

Upvotes: 0

Beth
Beth

Reputation: 9617

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

Upvotes: 0

user404463
user404463

Reputation: 136

This is a great resource for exactly what your asking.

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

Upvotes: -1

SQLMenace
SQLMenace

Reputation: 135111

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.......

Upvotes: 3

Related Questions