Please provide the efficient solution with query ?
Loading
Please provide the efficient solution with query ?
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Vishal YelvePosted Aug 5, 2022, 7:16 PM
Jaydeep PatilPosted Aug 7, 2022, 5:05 AM
Vishal JoshiPosted Aug 6, 2022, 7:39 PM
Sachin SinghPosted Aug 5, 2022, 6:18 PM
Vishal JoshiPosted Aug 5, 2022, 5:53 PM
DECLARE @BATCHSIZE INT, @WAITFORVAL VARCHAR(8), @ITERATION INT, @TOTALROWS INT, @MAXRUNTIME VARCHAR(8), @BSTOPATMAXTIME BIT, @MSG VARCHAR(500)
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4000
SET @WAITFORVAL = '00:00:10'
SET @MAXRUNTIME = '08:00:00' -- 8AM
SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME
SET @ITERATION = 0 -- LEAVE THIS
SET @TOTALROWS = 0 -- LEAVE THIS
WHILE @BATCHSIZE>0
BEGIN
-- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...
IF CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1
BEGIN
RETURN
END
DELETE TOP(@BATCHSIZE)
FROM SOMETABLE
WHERE 1=2
SET @BATCHSIZE=@@ROWCOUNT
SET @ITERATION=@ITERATION+1
SET @TOTALROWS=@TOTALROWS+@BATCHSIZE
SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)
RAISERROR (@MSG, 0, 1) WITH NOWAIT
WAITFOR DELAY @WAITFORVAL
END
DECLARE @BATCHSIZE INT, @WAITFORVAL VARCHAR(8), @ITERATION INT, @TOTALROWS INT, @MAXRUNTIME VARCHAR(8), @BSTOPATMAXTIME BIT, @MSG VARCHAR(500) SET DEADLOCK_PRIORITY LOW; SET @BATCHSIZE = 4000 SET @WAITFORVAL = '00:00:10' SET @MAXRUNTIME = '08:00:00' -- 8AM SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME SET @ITERATION = 0 -- LEAVE THIS SET @TOTALROWS = 0 -- LEAVE THISWHILE @BATCHSIZE>0 BEGIN-- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...IF CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1BEGINRETURNENDDELETE TOP(@BATCHSIZE)FROM SOMETABLEWHERE 1=2SET @BATCHSIZE=@@ROWCOUNTSET @ITERATION=@ITERATION+1SET @TOTALROWS=@TOTALROWS+@BATCHSIZESET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)RAISERROR (@MSG, 0, 1) WITH NOWAITWAITFOR DELAY @WAITFORVAL END