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

Accepted answer
Score: 23

The single delete will be faster for a few 1 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.
Score: 3

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

Score: 3

The single statement is faster for reasons 3 already stated.

In the example given


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

Score: 1

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

Score: 1

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

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

Score: 1

The single statement:

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

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

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

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

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

(or whatever)

Score: 0

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

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

DELETE FROM table_name WHERE X='?'

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

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

More Related questions