[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:

Only one plan will need to be generated
Only one transaction will be involved (if you are using them)
If you are running these from code, then there is less overhead with ODBC calls and network traffic
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

DELETE FROM table_name WHERE X BETWEEN 1 AND 3

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.

Source: stackoverflow.com

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

Should I add this new column to customers table or to a separate new table?

T-SQL String Replace

How to Delete all data from a table which contain self referencing foreign key

SQL Row_Number() function in Where Clause without ORDER BY?

Does a SELECT query always return rows in the same order? Table with clustered index

Converting Access Database to MSSQL Database Using Microsoft SQL Server Migration Assistant for Access

How are varchar values stored in a SQL Server database?

Sharing data between SQL databases

How to change database from Single user mode to multi user

How to test for multiple row actions in a SQL Server trigger?