delete records in batches - use rowcount??

[divinyl]

Hi all

I need to delete records from a table in batches of 5000.
The problem is that the id column for the table contains duplicates, so when i try this:

delete table1 from (select top 5000 * from table1) as t1 where table1.testid = t1.testid

it ends up deleting more than just 5000 rows.

I think i might need to use rowcount but im not really experienced in tsql so any assitance would be v. much appreciated!

Thanks
Di

 

[divinyl]

mm, actually what would be even nicer is if the script could loop until all records were deleted, in batches of 5000, with a minute delay between each delete.

Can anyone help me with this??

thanks!

 

[ca8msm]

Why do the rows need to be deleted in batches?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[divinyl]

because i actually need to delete from several tables as part of a development release, and the tables are all involved in replication. I'm worried that replication will be adversley affected if all the deletes occurred at once and the distirbution agent tried to push out the info to the subscribers all at once.

 

[ca8msm]

I'm pretty sure that replication shouldn't be greatly affected by removing all the records at once, but I'll leave it up to more experienced people to confirm this.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[maswien]

Not exactly know your situation, but you probably can do it like this:

<code>
alter table table1 add rowid int identity(1,1)

declare @line_num as int
while (@@rowcount > 0)
begin
select @line=min(rowid) + 5000 from table1
delete table1 where rowid < @line
end
</code>

 

[gmmastros]

>> with a minute delay between each delete.

[tt][blue]WaitFor Delay '0:01:00'[/blue][/tt]

Go ahead. Run that command. I dare ya. How much you wanna bet it takes exactly 1 minute to execute.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[divinyl]

Thanks for the responses guys. Here's what i have managed to come up with - hope it's useful to others out there. If anyone has any suggestions for improvements, let me know.

<code>

DECLARE @Count INT

SET ROWCOUNT 5000
SELECT @Count=count(*) from Table1
SELECT @Count=@Count+count(*) from Table2

WHILE @Count > 0
BEGIN

DELETE FROM Table1
if @@rowcount > 0
WAITFOR DELAY '00:00:30'

DELETE FROM Table2
if @@rowcount > 0
WAITFOR DELAY '00:00:30'

SELECT @Count=count(*) from Table1
SELECT @Count=@Count+count(*) from Table2

END
SET ROWCOUNT 0

</code>

 

[ESquared]

SET ROWCOUNT 5000

DELETE Table1
WHILE @@RowCount > 0 BEGIN
   WAITFOR DELAY '00:00:30'
   DELETE Table1
END

DELETE Table2
WHILE @@RowCount > 0 BEGIN
   WAITFOR DELAY '00:00:30'
   DELETE Table2
END

...

SET ROWCOUNT 0

If you really need to delete rows from the two tables at the same time, just run them as separate batches, no need to make this complicated.

You might also consider a smaller rowcount and a shorter delay, though that would be more overall overhead. There's probably a sweet spot of balance between size of the delete and the number of deletes, assuming that this is needed at all and replication can't handle it.

Batched deletes can also help alleviate blocking problems if other processes are trying to work with these tables.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]