Better approach for mass/big deletion in Dynamics 365 F&O

If you have to delete records from a table, how would you do that as a developer?

Thinking, may be to write a query as...….  Delete from TableName Where Clause/Condition

That seems like a fine solution but cannot be in every case. Let’s suppose if you have 1 million records in a table and you have to delete 90% of those records. Would you still write the query in a similar way??

That can exhaust your system in two ways.

1)      It will take a long time to delete 90% records from a total of 1 million records because behind the scene the SQL Server will write a log entry for each delete.

2)      The SQL Server Log file will keep growing, behind the scene, consuming the hard disk space.

You don’t want these two things to happen. You want the deletion of the records to be fast and with least consumption of space on the hard disk.

A good approach for such problems is the introduction of a third/temp table. You carry the process in four steps as follows.

1)      Dump/Insert all the records that you want to keep, into a new/temp table.

2)      Do mass delete by Truncate statement; deleting all data of the table in millisecond or seconds  

3)      Insert back data into your original table from the Temp table (that you wanted to retain)

4)      Drop the Temp Table

The Truncate statement is supported on the SQL Server. It deletes all data from a table quickly in seconds.

Microsoft’s Product team has done a good job, by following this approach for some processes in Dynamics 365.

An example is Clean up the batch job history

This batch job is calling a StoreProcedure, which exists on SQL Server. It is named as SysTruncateBatchHistory

You can see this store procedure in AXDB

It dumps all data that you want to retain into a temp table, and truncates all data from the original table. Finally, it inserts back in, the data from temp table to original table.

Here is the SQL Server code for the store procedure that cleans batch job history. You can see how it makes use of a third/temp table for fast deletion (using truncate statements).

USE [AXDB]
GO
/****** Object:  StoredProcedure [dbo].[SysTruncateBatchHistory]    Script Date: 2/15/2021 7:31:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SysTruncateBatchHistory](@daysRetention int)
AS
BEGIN

	DECLARE @RETENTIONBOUNDARY DATETIME
	SELECT @RETENTIONBOUNDARY = DATEADD(DAY, -@daysRetention, GETDATE())

	-- STEP 0. CHECK IF TEMP TABLES FROM PREVIOUS RUN STILL EXIST.
	-- IF SQL CRASHED AFTER STEP 4 AND BEFORE STEP 5, RETENTION TABLES
	-- WILL CONTAIN THE DATA THAT NEEDS TO BE INSERTED INTO ORIGINAL TABLES
	-- IF SQL CRASHED BETWEEN STEPS 1 AND 2, RETENTION TABLES WILL CONTAIN THE DATA
	-- THAT IS STILL PRESENT IN THE ORIGINAL TABLES, SO NEED TO CHECK IF ITS THE CASE

	IF OBJECT_ID(N'BATCHJOBHISTORYRETENTION', N'U') IS NOT NULL
	BEGIN
		BEGIN TRAN
			IF NOT EXISTS (SELECT RECID FROM BATCHJOBHISTORY WHERE RECID = (SELECT TOP 1 RECID FROM BATCHJOBHISTORYRETENTION))
			BEGIN
				INSERT INTO BATCHJOBHISTORY SELECT * FROM BATCHJOBHISTORYRETENTION
			END
			DROP TABLE BATCHJOBHISTORYRETENTION
		COMMIT
	END

	IF OBJECT_ID(N'BATCHHISTORYRETENTION', N'U') IS NOT NULL
	BEGIN
		BEGIN TRAN
			IF NOT EXISTS (SELECT RECID FROM BATCHHISTORY WHERE RECID = (SELECT TOP 1 RECID FROM BATCHHISTORYRETENTION))
			BEGIN
				INSERT INTO BATCHHISTORY SELECT * FROM BATCHHISTORYRETENTION
			END
			DROP TABLE BATCHHISTORYRETENTION
		COMMIT
	END

	IF OBJECT_ID(N'BATCHCONSTRAINTSHISTORYRETENTION', N'U') IS NOT NULL
	BEGIN
		BEGIN TRAN
			IF NOT EXISTS (SELECT RECID FROM BATCHCONSTRAINTSHISTORY WHERE RECID = (SELECT TOP 1 RECID FROM BATCHCONSTRAINTSHISTORYRETENTION))
			BEGIN
				INSERT INTO BATCHCONSTRAINTSHISTORY SELECT * FROM BATCHCONSTRAINTSHISTORYRETENTION
			END
			DROP TABLE BATCHCONSTRAINTSHISTORYRETENTION
		COMMIT
	END

	-- STEP 1. SAVE RETENTION DATA
	SELECT * INTO BATCHJOBHISTORYRETENTION FROM BATCHJOBHISTORY
		WHERE CREATEDDATETIME >= @RETENTIONBOUNDARY
	DECLARE @LASTRECORD_BATCHJOBHISTORY BIGINT
	SELECT @LASTRECORD_BATCHJOBHISTORY = MAX(RECID) FROM BATCHJOBHISTORYRETENTION

	SELECT * INTO BATCHHISTORYRETENTION FROM BATCHHISTORY
		WHERE EXISTS (SELECT RECID FROM BATCHJOBHISTORYRETENTION JOB WHERE JOB.RECID = BATCHHISTORY.BATCHJOBHISTORYID)
	DECLARE @LASTRECORD_BATCHHISTORY BIGINT
	SELECT @LASTRECORD_BATCHHISTORY = MAX(RECID) FROM BATCHHISTORYRETENTION

	SELECT * INTO BATCHCONSTRAINTSHISTORYRETENTION FROM BATCHCONSTRAINTSHISTORY
		WHERE EXISTS (SELECT RECID FROM BATCHHISTORYRETENTION WHERE BATCHHISTORYRETENTION.RECID = BATCHCONSTRAINTSHISTORY.BATCHID)
	DECLARE @LASTRECORD_BATCHCONSTRAINTSHISTORY BIGINT
	SELECT @LASTRECORD_BATCHCONSTRAINTSHISTORY = MAX(RECID) FROM BATCHCONSTRAINTSHISTORYRETENTION

	-- STEP 2. COPY THE DELTA WITH LOCKING HISTORY TABLES
	BEGIN TRAN

	INSERT INTO BATCHJOBHISTORYRETENTION
		SELECT * FROM BATCHJOBHISTORY WITH (TABLOCK, HOLDLOCK) WHERE RECID > @LASTRECORD_BATCHJOBHISTORY

	INSERT INTO BATCHHISTORYRETENTION 
		SELECT * FROM BATCHHISTORY WITH (TABLOCK, HOLDLOCK) WHERE RECID > @LASTRECORD_BATCHHISTORY

	INSERT INTO BATCHCONSTRAINTSHISTORYRETENTION
		SELECT * FROM BATCHCONSTRAINTSHISTORY WITH (TABLOCK, HOLDLOCK) WHERE RECID > @LASTRECORD_BATCHCONSTRAINTSHISTORY
		
	-- STEP 3. TRUNCATE ORIGINAL TABLES
	TRUNCATE TABLE BATCHJOBHISTORY
	TRUNCATE TABLE BATCHHISTORY
	TRUNCATE TABLE BATCHCONSTRAINTSHISTORY

	-- STEP 4. RELEASE LOCKS
	COMMIT

	-- STEP 5. RESTORE SAVED DATA
	INSERT INTO BATCHJOBHISTORY SELECT * FROM BATCHJOBHISTORYRETENTION
	DROP TABLE BATCHJOBHISTORYRETENTION

	INSERT INTO BATCHHISTORY SELECT * FROM BATCHHISTORYRETENTION
	DROP TABLE BATCHHISTORYRETENTION

	INSERT INTO BATCHCONSTRAINTSHISTORY SELECT * FROM BATCHCONSTRAINTSHISTORYRETENTION
	DROP TABLE BATCHCONSTRAINTSHISTORYRETENTION
END