[ACCEPTED]-Which is faster? Multiple DELETE statements or a single DELETE Statement using "IN ('x', ''y')-database
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.
A single delete is faster, there is only 1 one plan to create and execute.
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.
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.
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.
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)
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