Deleting 3 million data takes a lot of time

Question

I want to delete three million data on data basis in sql server. I am using a very simple query and batch deleting the files but the command is executing and still running for more than 3 hours. What could be the possible reasons for such low performance. Please find the code below that I am using

Create PROC [dbo].[DeleteOneWeekOldData] 
@StartDate DateTime,
@EndDate DateTime

AS

DECLARE @continue INT
DECLARE @rowcount INT

SET @continue = 1
WHILE @continue = 1
BEGIN
    SET ROWCOUNT 1000
    BEGIN TRANSACTION
    DELETE FROM WorkflowContentDetails WHERE  StartDateTime BETWEEN @StartDate AND @EndDate
    SET @rowcount = @@rowcount 
    COMMIT
       IF @rowcount = 0
    BEGIN
        SET @continue = 0
    END
END

GO

Answer 1

There are two problems with your query.

1. SET ROWCOUNT 1000 causes the data to be deleted in very small chunks. If you need to delete 1M rows, that would be 1000 delete operations. SQL Server will better handle small number of big operations, than large number of small operations. Besides, SET ROWCOUNT will no more affect insert/update/delete operations in future releases.

2. StartDateTime BETWEEN @StartDate AND @EndDate is being executed every time. If there's no index on that field, it could take a lot of time. Instead of that, it would be better to first select values from Primary Key column (if you have one, and if there's (clustered)index defined on it) into a temp table and then work it in a loop - with condition on indexed column it would work few times faster.

Answer 2

Your best performance in this case will be to partition the table by date, and then truncate or drop the partitions when they are no longer needed (rather than using the DELETE statement).

The reasons for the performance gain are:

1) By specifying which partition to truncate, by definition you are also defining the date range, so SQL Server does not need to hunt for it, whether with an index-based retrieval or a table scan, either of which will take time.

2) The TRUNCATE operation is DDL, not DML like DELETE, so the operation does not get written to transaction logs, making it much faster. It also does not carry the risk of filling up your log files. (Of course this may have implications for incremental backups and replication, if you are are using either of those.)

In the past, I have seen a TRUNCATE operation complete in less than a minute where the corresponding DELETE took hours.

A walk-through of the strategy is available here: http://www.galaxysql.com/2013/09/sql-server-partitioning-for-performance-and-archiving/

Answer 3

A few things that can cause delete to be slow. You already read about it in the comments.

For ex. you need 3M rows / 1k rows = 3000 times to do search without idndex. And what's more, you're imposes an exclusive lock on the table 3000 times because of the absence an index.

This is my "DELETE" pattern on production. It solves these problems, even if there is no suitable index. A lock is only on the primary key and only during delete.

SET NOCOUNT ON

DECLARE 
    @StartDate DateTime = '20130101',
    @EndDate DateTime = '20131201'

DECLARE @chunk_size bigint = 1000
DECLARE @RowCount bigint;
DECLARE @delay      DATETIME = '00:00:01'       --- 1 second by defaul, Used for delaying the updates inside the loop, can be 0

DECLARE @Chunk_IDs as TABLE (
    [ID] int NOT NULL,
    [SourcePKID] int not null
);

IF OBJECT_ID('tempdb..#temp_OldDataIDs') is not null
    DROP TABLE #temp_OldDataIDs;

CREATE TABLE #temp_OldDataIDs (
    [ID]            INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    [SourcePKID]    INT NOT NULL,
    [DeletedStatus] BIT NOT NULL DEFAULT 0);

INSERT INTO #temp_OldDataIDs ([SourcePKID])
SELECT [ID]
FROM WorkflowContentDetails 
WHERE StartDateTime BETWEEN @StartDate AND @EndDate

SET @RowCount = @@ROWCOUNT;

CREATE NONCLUSTERED INDEX IX_#temp_OldDataIDs on #temp_OldDataIDs ([DeletedStatus]) include([ID],SourcePKID)

WHILE (@RowCount != 0)
BEGIN

    DELETE @Chunk_IDs;

    INSERT INTO @Chunk_IDs ([ID], [SourcePKID])
    SELECT TOP (@chunk_size)
        [ID], [SourcePKID]
    FROM #temp_OldDataIDs
    WHERE [DeletedStatus] = 0
    ORDER BY [ID];

    SET @RowCount = @@ROWCOUNT;
    IF @RowCount = 0 BREAK;


    DELETE WorkflowContentDetails
    FROM WorkflowContentDetails
        INNER JOIN @Chunk_IDs ChunkIDs ON WorkflowContentDetails.[ID] = ChunkIDs.[SourcePKID];

    UPDATE OldIDs
        SET [DeletedStatus] = 1
    FROM #temp_OldDataIDs OldIDs
        INNER JOIN @Chunk_IDs ChunkIDs ON OldIDs.[ID] = ChunkIDs.[SourcePKID];

-- debug
-- PRINT CAST(@RowCount as varchar(30)) + ' ' + CONVERT(varchar(30), GETDATE(),121)

    -- The requested delay will hold the loop here as requested.
    WAITFOR DELAY  @delay
END


GO

Answer 4

try this,why r using loop ?purpose ?

Create PROC [dbo].[DeleteOneWeekOldData] 
@StartDate DateTime,
@EndDate DateTime

AS
begin
Set NoCount on
DECLARE @continue INT
DECLARE @rowcount INT
Declare @err int
--SET @continue = 1 --why ar u using these commented part
--WHILE @continue = 1
--BEGIN
--    SET ROWCOUNT 1000
    BEGIN TRANSACTION
    DELETE FROM WorkflowContentDetails WHERE  StartDateTime BETWEEN @StartDate AND @EndDate
    SET @err = @@Error
    if(@err<>0)
    COMMIT
      else
       rollback
    END

GO