Menu
I have a table with more than 20 million rows, and when i do:
DELETE [Table] WHERE ID = ?
It takes over 40 seconds. The ID column is clustered.
Is this what you could expect? or is it possible to optimize this?
904
Indexes can make every operation in the database faster, even deletes.
If you need to remove 10 million rows and have 1 GB of log space available use Delete TOP(10000) From dbo. myTable (with your select clause) and keep running it till there are no more rows to delete.
Clustering indexes can improve the performance of most query operations because they provide a more linear access path to data, which is stored in pages. In addition, because rows with similar index key values are stored together, sequential detection prefetching is more efficient when clustering indexes are used.
In addition to the fine points JNK included in their answer, one particular killer I've seen is when you're deleting rows from the referenced table for one or more foreign key constraints, and the referencing column(s) in the referencing table(s) aren't indexed - you're forcing a table scan on each of those tables to occur before the delete can be accepted.
72
Also try deleting data in a batch. Example
set rowcount 10000
delete [table] where id = ?
while @@rowcount >0
begin
delete [table] where id = ?
end
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
