SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--
--  Create a new stored Refresh Delete procedure which will catch the deadlock 
--  victim abort and restart the delete transaction.
--  Bryon fixed on 29 October 2024

CREATE PROCEDURE RF2
       @current_execution INTEGER, @delete_sets INTEGER, @parallel_executions INTEGER, @total_executions INTEGER
AS
BEGIN

DECLARE @startdate DATE
DECLARE @enddate DATE
DECLARE @edate DATE
DECLARE @rangeStart INTEGER
DECLARE @subRangeSize INTEGER
DECLARE @range INTEGER

declare @success INTEGER
declare @index INTEGER
declare @div INTEGER
declare @mod INTEGER
declare @skip INTEGER

declare @streamRangeSize INTEGER
declare @sql NCHAR(1000)
declare @orderSql NCHAR(1000)
declare @liSql NCHAR(1000)

DECLARE @ErrorMessage    NVARCHAR(4000)
DECLARE @ErrorNumber     INT
DECLARE @ErrorSeverity   INT
DECLARE @ErrorState      INT
DECLARE @ErrorLine       INT
DECLARE @ErrorProcedure  NVARCHAR(200)

--Range size
SET @skip = @total_executions/@parallel_executions
--Index mapping current execution number to starting range
SET @div = floor((@current_execution-1)/@parallel_executions)
SET @mod = (@current_execution - 1) - @div * @parallel_executions
SET @index = @mod*@skip + @div + 1

--Debug instrumentation
--print N'@skip ' + CAST(@skip as NVARCHAR(5)) 
--print N'@div ' + CAST(@div as NVARCHAR(5))  
--print N'@index ' + CAST(@index as NVARCHAR(5))  

SET @sql = N'SELECT @sdate = dateadd(day,-1,min(O_ORDERDATE)), @edate = max(O_ORDERDATE) 
		FROM NEWOLDORDERS'
EXEC sp_executesql @sql,N'@sdate DATE output, @edate DATE output',@startdate output, @enddate output
--Debug instrumentation
--	print N'@startdate ' + CAST(@startdate as NVARCHAR(10))  
--	print N'@enddate ' + CAST(@enddate as NVARCHAR(10))  
--	print N'diff ' + cast(datediff(day, @startdate, @enddate) as NVARCHAR(5))

SET @streamRangeSize = datediff(day, @startdate, @enddate)/@parallel_executions + 1
--print N'@streamRangeSize ' + CAST(@streamRangeSize as NVARCHAR(5))  


IF (@total_executions > @parallel_executions)
	BEGIN
	SET @div = (@index-1)/@skip
	SET @mod = (@index-1) - @div * @skip
-- added plus one to the range to get the extra days.
	SET @subRangeSize = @streamRangeSize / @skip + 1

--Debug instrumentation
--	print N'@div ' + CAST(@div as NVARCHAR(5))  
--	print N'@mod ' + CAST(@mod as NVARCHAR(5))  
--	print N'@subRangeSize ' + CAST(@subRangeSize as NVARCHAR(5))  
	SET @rangeStart = @div * @streamRangeSize + @mod * @subRangeSize
	IF (@subRangeSize < 0)
		SET @subRangeSize = 0
	IF (@delete_sets <= 0)
		SET @delete_sets = 1
	END
ELSE
	BEGIN
	SET @subRangeSize = @streamRangeSize
	SET @rangeStart = @subRangeSize * (@index - 1)
	END

SET @startdate = dateadd(day, @rangeStart, @startdate)
IF (@index < @total_executions)
	SET @enddate = dateadd(day, @subRangeSize, @startdate)

SET @range = datediff(day, @startdate, @enddate) / @delete_sets

--Debug instrumentation
--print N'rangeSize ' + CAST(@subRangeSize as NVARCHAR(5)) + N' rangeStart ' + CAST(@rangeStart as NVARCHAR(5)) + N' range ' +  CAST(@range as NVARCHAR(5))
--print N'@deleteSets ' + CAST(@delete_Sets as NVARCHAR(5))
--
--  This handles the case when the max-min/delete_sets is less that 1
--
IF @range = 0
	SET @range = 1

--
--  Loop through the order keys deleting sets from orders 
--  and lineitem tables
--

Declare @shipdatesdate date 
Declare @shipdateedate date 
SET @edate = dateadd(day, @range, @startdate)

--Tuning Note: maxdop can be tuned to take into account the number of streams
SET @liSql = N'DELETE FROM LINEITEM where L_ORDERKEY  in 
				(select O_ORDERKEY from NEWOLDORDERS WHERE L_ORDERKEY = O_ORDERKEY 
			 and NEWOLDORDERS.O_ORDERDATE between  @startdate AND @edate)
			 and LINEITEM.L_SHIPDATE >= cast(@shipdatesdate as date) AND LINEITEM.L_SHIPDATE <= cast(@shipdateedate as date)
			option (hash join,maxdop 20)'

SET @orderSql = N'DELETE FROM ORDERS where O_ORDERKEY  in 
				(select O_ORDERKEY from NEWOLDORDERS WHERE O_ORDERKEY = O_ORDERKEY 
				and NEWOLDORDERS.O_ORDERDATE >= @startdate AND NEWOLDORDERS.O_ORDERDATE <= @edate)
			 and ORDERS.O_ORDERDATE >= @startdate AND ORDERS.O_ORDERDATE <= @edate
			option (hash join,maxdop 20)'

WHILE @startdate < @enddate
	BEGIN

  	DELETE_TRANS:
	SET @success = 1
  	BEGIN TRANSACTION

	BEGIN TRY
		-- Recompute our shipdate range each time order date range is modified (i.e. each iteration)
		select @shipdatesdate=dateadd(day,-1,min(L_SHIPDATE)),@shipdateedate=max(L_SHIPDATE) From NEWOLDORDERS where O_ORDERDATE between @startdate and @edate

		EXEC sp_executesql @liSql, N'@shipdatesdate DATE, @shipdateedate DATE,@startdate DATE, @edate DATE', @shipdatesdate, @shipdateedate, @startdate, @edate
		EXEC sp_executesql @orderSql, N'@startdate DATE, @edate DATE', @startdate, @edate
		select @liSql
		select @orderSql
		select @startdate, @edate,@shipdatesdate,@shipdateedate
	END TRY
	BEGIN CATCH
		SET @success = 0
		IF (error_number() = 1205) 	-- deadlock victim
			BEGIN
			PRINT 'Insert deadlock - restarting RF2'
			IF (@@trancount>0)
				ROLLBACK TRANSACTION
			END
		ELSE
			BEGIN			-- not a deadlock
			IF (@@trancount>0)
				ROLLBACK TRANSACTION
			END
	END CATCH

	IF (@success = 0)			-- deadlock - redo the inserts
		GOTO DELETE_TRANS

	COMMIT TRANSACTION

	SET @startdate = @edate
	SET @edate = dateadd(day, @range, @edate)

  	IF (@edate > @enddate)
		SET @edate = @enddate

  	END
END

GO


