Which is faster? Multiple DELETE statements or a single DELETE Statement using "IN ('x', ''y')"

Question

Just wondering which is faster:

DELETE FROM table_name WHERE X='1'
DELETE FROM table_name WHERE X='2'
DELETE FROM table_name WHERE X='3'

or

DELETE FROM table_name WHERE X IN ('1', '2', '3')

Any ideas, hints, or references where to read?

Thank you all!

Answer 1

A single delete is faster, there is only one plan to create and execute.

Answer 2

The single delete will be faster for a few reasons:

1. Only one plan will need to be generated
2. Only one transaction will be involved (if you are using them)
3. If you are running these from code, then there is less overhead with ODBC calls and network traffic
4. Any indexes will need to be refreshed just once, not many times.

Answer 3

The single statement is faster for reasons already stated.

In the example given

DELETE FROM table_name WHERE X BETWEEN 1 AND 3

will be even faster, especially if you have a clustered index on X.

Answer 4

Profile it in your DB with your indexes and your data. I'm inclined to agree with using a single statement, though, I can think of a few quick instances were the 3 statements would be much faster. If it really matters, profile it.

Answer 5

A single delete is generally faster for the reasons kogus mentioned.

But ... keep in mind that if you have to wipe out 50% of a 2 million row table and there is a lot of activity against the table, deleting in small batches or selecting into a new table and swapping that table in may be better approaches.

Answer 6

The single statement:

DELETE FROM table_name WHERE X IN ('1', '2', '3')

...would be faster. I'm not sure what database you're using, but I'd recommend looking into the execution plan of your queries. If you're using MySQL, you can use the EXPLAIN command like:

EXPLAIN DELETE FROM table_name WHERE X IN ('1', '2', '3')

Also as you've wrote in the comments if you're looking to dynamically fill you IN() clause you can use a subquery like:

DELETE FROM table_name WHERE x IN (SELECT id FROM table_name WHERE Y = Z)

(or whatever)

Answer 7

There is a third option that I think you may want to consider. I think it may be better then your first example, but not as good as your second.

If your database supports using prepared statements then you could do a prepare on the statement.

DELETE FROM table_name WHERE X='?'

Then simply calling that with value of 1, 2, and 3.

Generally my experience has been that you get the best performance when you use a set-based operation like your second example.