Looping?
-
I need to run a delete 6million rows from a table and need to do it in increments of 5000..
How could I send this in a loop?
set rowcount 5000
delete from tables
waitfor 2:00
-
If you need to delete all rows in the table use TRUNCATE TABLE.
If you need to leave minor number of rows copy them to temp table, do TRUNCATE and copy those rows back.
If you have FK's drop it before TRUNCATE and recreate them afterwards.
_____________
Code for TallyGenerator -
I'm deleting 6 out of 12 million, so I'm not truncating..
Can I loop the above statement or not?
-
>>I'm deleting 6 out of 12 million, so I'm not truncating..
Yes, but when it comes to large numbers of deletions, it is often faster to select out the records you want to keep, truncate the table (fast, unlogged operation), then copy the kept records back.
But yes, you can loop 5000 at a time if you wish. Use a locally declared variable to keep track of deletion count:
Declare @DeletedCount As int
Set @DeletedCount = 0
Set Rowcount 5000
While @DeletedCount <= 6000000
Begin
Your Delete Statement Goes Here
Select @DeletedCount = @DeletedCount + @@RowCount
End
-
If you don't know how many rows will be deleted, you can do something like this...
SET ROWCOUNT 5000 SELECT 1 --"Primes" the rowcount so the loop works WHILE @@ROWCOUNT > 0 BEGIN WAITFOR DELAY '00:02:00' --Delay of 2 minutes could certainly be changed to less your conditional delete statement goes here and NOTHING MUST FOLLOW! END
Please test it somewhere else besides "production", eh?
As suggested though, DELETEs are, quite possibly, the most expensive thing you can do to a table because EVERY index is affected and must be updated. It would be much quicker to copy the good records into a new table and then do a "snap" rename of the old and new tables. Inherently, that will also make a backup for you. The rename process, when done correctly, will take about 65 milliseconds to accomplish.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
If you decide to delete the rows (I agree with Jeff that copy/rename is often the best way to do this), one possibility is to create a job - using ROWCOUNT 5000, but without a loop, just a single delete statement - and schedule it to run every few minutes. One of advantages of this solution is that you can schedule it to run e.g. every 10 minutes during the day and every 1 minute during the night, or only at night, disable or restart it easily etc. This gives you more control over it and you can pick the time when traffic is low for the execution.
-
One other BIG thing to consider before you delete anything... do you have a permanent backup of the data you are about to delete? One of the 10 commandments for living in the world of Data Trolls is "Thou shalt NOT delete... thou shalt archive instead". Basically, move the data to be deleted to an archive and then delete from the table because as sure as I'm writing this, somebody is going to ask for that data. If it's permanently deleted, you'll need an awfully big wash cloth to get all the egg of your face. Sounds like the voice of experience, huh?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
This was a part of an 'Archiving' process. We had already move the data were were deleting over to our archive server, but needed to delete that data out of production with no downtime to users. So I had to keep my rowcount log and add a waitfor so I wouldn't lock the table.
Thanks for everyone's help
Susan
-
Got it! Thanks for the feedback, Susan.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply