Slow delete with immediate output

-- Slow delete example, 1 day at a time
-- Set initial &amp; final value
-- RAISERROR instead of PRINT for immediate output

DECLARE	@days_to_keep	SMALLINT = 10
		,@loop_value	SMALLINT = 100
		,@starttime		datetime
		,@strMsg		NVARCHAR(100)

WHILE @loop_value > @days_to_keep
BEGIN
	-- Replace PRINT function
	SELECT @strMsg = 'Current loop value ' + CAST(@loop_value as varchar(10))
	RAISERROR (@strMsg, 0, 1) WITH NOWAIT

	SET @starttime= GETDATE();

	DELETE
	FROM   dbo.MyTable
	WHERE  CheckDate &lt; dateadd(dd, -@days_to_keep, getdate());

	SET @loop_value = @loop_value -1;

	-- Replace PRINT function
	SELECT @strMsg = 'Time in sec: ' + CAST(datediff(ss,@starttime,getdate()) as varchar(10))
	RAISERROR (@strMsg, 0, 1) WITH NOWAIT
	WAITFOR DELAY '00:01'

END

-- Slow delete example, 1 day at a time

-- Set initial & final value

-- RAISERROR instead of PRINT for immediate output

DECLARE @days_to_keep SMALLINT = 10

,@loop_value SMALLINT = 100

,@starttime datetime

,@strMsg NVARCHAR(100)

WHILE @loop_value > @days_to_keep

BEGIN

-- Replace PRINT function

SELECT @strMsg = 'Current loop value ' + CAST(@loop_value as varchar(10))

RAISERROR (@strMsg, 0, 1) WITH NOWAIT

SET @starttime= GETDATE();

DELETE

FROM dbo.MyTable

WHERE CheckDate < dateadd(dd, -@days_to_keep, getdate());

SET @loop_value = @loop_value -1;

-- Replace PRINT function

SELECT @strMsg = 'Time in sec: ' + CAST(datediff(ss,@starttime,getdate()) as varchar(10))

RAISERROR (@strMsg, 0, 1) WITH NOWAIT

WAITFOR DELAY '00:01'

END

This entry was posted in T-SQL. Bookmark the permalink.

Slow delete with immediate output

Leave a Reply Cancel reply

Recent Posts

Archives

Categories