jonathanmarenus (5/22/2016)
I need something more efficient, so I then tried the following, but the performance was even worse:delete <tablename> a
where exists
(
select a.field1, a.field2.........a.field30
intersect
select b.field1, b.field2.........b.field30
from <secondtablename> b
)
That's a weird way of doing an exists, it's requiring an extra scan over table a in return for less typing (maybe less typing).
DELETE TableA
WHERE EXISTS (SELECT 1 FROM TableB WHERE TableA.Field1=TableB.Field1 AND TableA.Field2 = TableB.Field2 AND ...)
It's not going to be pretty though. Is this some archiving or background process?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability