Technical Article

Deleting Sets of Rows in huge table

,

There was a problema user was reflecting

http://groups.yahoo.com/group/sqlcon/message/1119

What I did was following steps

1. Divide the count of table into segments for divide
2. Then use the even Odd functionality for smooth looping
3. Use a simple while loop and SET ROWCOUNT functionality for further deletions across table with checkpoint
4. It saved the log swell up and resources moderation of SQL
5.BUt make sure DB is Simple Recovery model as well so that it can curb log growth and also please test the script in UAT rather than Production directly

declare @Cnt int
Select @cnt = (select Count(*) from authorstest nolock)
print @cnt
declare @rset int
declare @stper int
/*
Lets divide the total deleted rows into chinks of three or four called segments
We will intialise the Segments here and then loop through them and
 issue checkpoint after every segment
*/select @stper = ABS(@cnt) % 2 
if  @stper = 1 
Begin 
select @rset = (@cnt + 1 )/4
        select @cnt = @cnt + 1 
print @rset
End
Else
       Begin 
        select @rset = (@cnt)/4
       End

select @stper = (@cnt/@rset)
print @stper
--Check the segment length and increase it or  decrease it based on your requirement
Print 'Printing innerloop here'
While ( @rset > 0 )
BEgin
print @rset
Select @rset = @rset - 1

SET ROWCOUNT @rset
delete from authorstest
checkpoint
print 'Checkpoint issued'   
SET ROWCOUNT 0
print @rset
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating