Optimizing Delete on SQL Server

The following article, Fast Ordered Delete Operations may be of interest to you.

Performing fast SQL Server delete operations

The solution focuses on utilising a view in order to simplify the execution plan produced for a batched delete operation. This is achieved by referencing the given table once, rather than twice which in turn reduces the amount of I/O required.

T-SQL Optimize DELETE of many records

The log problem is probably due to the number of records deleted in the trasaction, to make things worse the engine may be requesting a lock per record (or by page wich is not so bad)

The one big thing here is how you determine the records to be deleted, i'm assuming you use a datetime field, if so make sure you have an index on the column otherwise it's a sequential scan of the table that will really penalize your process.

There are two things you may do depending of the concurrency of users an the time of the delete

If you can guarantee that no one is going to read or write when you delete, you can lock the table in exclusive mode and delete (this takes only one lock from the engine) and release the lock
You can use batch deletes, you would make a script with a cursor that provides the rows you want to delete, and you begin transtaction and commit every X records (ideally 5000), so you can keep the transactions shorts and not take that many locks

Take a look at the query plan for the delete process, and see what it shows, a sequential scan of a big table its never good.

Delete statement in SQL is very slow

Things that can cause a delete to be slow:

deleting a lot of records
many indexes
missing indexes on foreign keys in child tables. (thank you to @CesarAlvaradoDiaz for mentioning this in the comments)
deadlocks and blocking
triggers
cascade delete (those ten parent records you are deleting could mean
millions of child records getting deleted)
Transaction log needing to grow
Many Foreign keys to check

So your choices are to find out what is blocking and fix it or run the deletes in off hours when they won't be interfering with the normal production load. You can run the delete in batches (useful if you have triggers, cascade delete, or a large number of records). You can drop and recreate the indexes (best if you can do that in off hours too).

SQL delete query optimization

DELETE edms_personalisation p 
WHERE  NOT EXISTS (SELECT 'X' 
                   FROM   edms_mail m 
                   WHERE  m.mail_id = p.mail_id) 
       AND rownum <= 25000;

DELETE edms_personalisation 
WHERE  mail_id IN (SELECT mail_id FROM edms_personalisation 
                   MINUS 
                   SELECT mail_id FROM edms_mail) 
       AND rownum <= 25000;

If Oracle I would have written a PL/SQL to bulk collect all the qualifying mail ids to be deleted.And make a FORALL DELETE querying the index directly(Bulk Binding). You can do it in batch too.

Otherwise since the 'to be deleted' table is too big, wiser to copy the good data into temp table, truncate the table, and reload it from temp. When it has to be done in a frequent cycle, the above methods have to be used!

Try this! Good Luck!

Optimizing delete query by order

In general, deleting a bunch of rows takes time. There is a lot of overhead for removing rows -- particularly logging and locking, but maintaining indexes, triggers, and foreign keys also adds overhead.

The most common technique for handling this uses table partitions. Basically, the table is stored in separate pieces, in this case by time -- say one piece per week or per month. The database handles the partitioning to make it efficient.

Then, deleting the rows from a particular period is simply a matter of dropping a partition. This is usually much, much, much faster than deleting the records.

This does require re-constructing the table (a one-time cost). The exact syntax and details depends on the database you are using. But this should point you in a direction to fix your problem.

Improving performance of Sql Delete

Consider running this in batches. A loop running 1000 records at a time may be much faster than one query that does everything and in addition will not keep the table locked out to other users for as long at a stretch.

If you have cascade delete (and lots of foreign key tables affected) or triggers involved, you may need to run in even smaller batches. You'll have to experiement to see which is the best number for your situation. I've had tables where I had to delete in batches of 100 and others where 50000 worked (fortunate in that case as I was deleting a million records).

But in any even I would put my key values that I intend to delete into a temp table and delete from there.

Optimizing delete operation of 50 millions of records from table

If the number of records you want to preserve is small comparing to number of records you need to delete, I would copy them to temporary table, then TRUNCATE the original table and insert preserved records back from temporary table. Table truncation works very fast, so you may save much time. However TRUNCATE TABLE not always work, but it can be a solution for you.

Optimize delete SQL query with unordered table

It is probably not the query itself. Your code is deleting about 600+ records per second. A lot is going on in that time -- logging, locking, and so on.

A faster approach is to load the data you want into a new table, truncate the old table, and reload it:

select *
into temp_huge_table 
from huge_table
where request_dt > ?;  -- whatever the cutoff is

Then -- after validating the results -- truncate the huge table and reload the data:

truncate table huge_table;

insert into huge_table
    select *
    from temp_huge_table;

If there is an identity column you will want to disable that to allow identity insert. You might have to take other precautions if there are triggers that set values in the table. Or if there are foreign key references to rows in the table.

I would not suggest doing this directly. After you have truncated the table, you should probably partition by the table by date -- by day, week, month, whatever.

Then, in the future, you can simply drop partitions rather than deleting rows. Dropping partitions is much, much faster.

Note that loading a few tens of millions of rows into an empty table is much, much faster than deleting them, but it still takes time (you can test how much time on your system). This requires downtown for the table. However, you hopefully have a maintenance period where this is possible.

And, the downtime can be justified by partitioning the table so you won't have this issue in the future.

Optimizing Delete on SQL Server