0

If I have two very large tables (TableA and TableB), both with an Id column, and I would like to remove all rows from TableA that have their Ids present in TableB. Which would be the fastest? Why?

--ISO-compatible
DELETE FROM TabelA   
WHERE Id IN (SELECT Id FROM TableB)

or

-- T-SQL
DELETE A FROM TabelA AS A  
INNER JOIN TableB AS B  
ON A.Id = B.Id
MOS
  • 137
  • 1
  • 8
  • 1
    You should run the queries on your data on your system. I would expect the performance to be pretty similar under most circumstances, but if you really care, then try both out. – Gordon Linoff Feb 23 '17 at 13:02
  • 1
    The query syntax doesn't matter - the execution plan created from it is what matters. If the execution plan is the same, the performance will be identical – Panagiotis Kanavos Feb 23 '17 at 13:20

3 Answers3

2

If there are indexes on each Id, they should perform equally well.

If there are not indexes on each Id, exists() or in () may perform better.

In general I prefer exists() over in () because it allows you to easily add more than one comparison when needed. 

delete a
from tableA as a
where exists (
  select 1
  from tableB as b
  where a.Id = b.Id
  )

Reference:

SqlZim
  • 37,248
  • 6
  • 41
  • 59
1

As long as your Id in TableB is unique, both queries should create the same execution plan. Just include the execution plan to each queries and verify it.

Take a look at this nice post: in-vs-join-vs-exists

Raul
  • 2,745
  • 1
  • 23
  • 39
1

There's an easy way to find out, using the execution plan (press ctrl + L on SSMS).

Since we don't know the data model behind your tables (the eventual indexes etc), we can't know for sure which query will be the fastest.

By experience, I can tell you that, for very large tables (>1mil rows), the delete clause is quite slow, because of all the logging. Depending on the operation you're doing, you will want SQL Server NOT TO log the delete.

You might want to check at this question : How to delete large data of table in SQL without log?

Community
  • 1
  • 1
Paul Daubian
  • 91
  • 1
  • 5