| Author |
 Topic  |
|
|
mivey4
Yak Posting Veteran
USA
53 Posts |
 Posted - 10/17/2007 : 22:26:44
|
Hi.
I'd like to know what is the best recommended and efficient method of deleting a very large number of rows (like 70,000 or more) from a table.
The first obvious thing I'd imagine would be to perform the delete operation on an indexed column but since a delete operation would write to the transaction logs, having enough allocated space on the disk where the transaction logs are would be another concern as well.
I have been told that to insert the records into a temp table would increase the performance and minimize the time required to perform the delete but I don't fully understand how this would work, if its the best method to use or how to do this correctly.
So my question is. What is the best method of deleting a large number of rows from a table and maximize the performance while minimizing the amount of time required to perform the operation?
Any professional suggestions would be most appreciated.
|
|
|
Julien.Crawford
Starting Member
Australia
14 Posts |
Posted - 10/17/2007 : 22:32:05
|
-- You could try this sort of thing.
-- It means you are actually committing, so any rollback options are gone - frequently thats okay for deletes (But be sure)
set rowcount 5000
while 1=1
begin
begin tran
delete from X
commit
if @@rowcount = 0
break
end
set rowcount 0
|
 |
|
|
dinakar
Flowing Fount of Yak Knowledge
USA
2347 Posts |
Posted - 10/17/2007 : 22:36:52
|
do it in batches.. use SET ROWCOUNT to limit the rows...
WHILE EXISTS ( SELECT * FROM table WHERE <condition to delete>)
BEGIN
SET ROWCOUNT 1000
DELETE Table WHERE <Condition>
SET ROWCOUNT 0
ENd
You can also create a job to back up log with truncate only option and let it run every minute so your log file doesnt blow up..
Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/ |
|
|
|
mivey4
Yak Posting Veteran
USA
53 Posts |
Posted - 10/17/2007 : 23:03:09
|
Wow! Thanks. I believe both responses are fairly the same in the general concept of performing the deletion by breaking the operation into batches. Dinakar's example I clearly understand and I will definitely try this; but could you provide a little more explanation for your sample Julien? It seems as though you are setting up an infinite loop by setting 1=1 Could you please explain this?
Delete from X, I believe or understand would be the actual delete statement with the where condition to constrain the number of rows to be deleted and a commit is performed after each row has been deleted until the @@rowcount variable reaches 0 indicating there aren't any more rows to delete based on the query. Is this correct?
If you could provide just a bit more of an explanation of your code, I'd like to test it as well.
Also one last question. Do either of you have a personal preference for the number of rows that should be included in a batch? 1000 or 5000?
Thanks! |
|
|
|
Kristen
Test
United Kingdom
19683 Posts |
|
|
mivey4
Yak Posting Veteran
USA
53 Posts |
Posted - 10/18/2007 : 05:52:18
|
| I finally figured out the logic of the Julien.Crawford post and thanks for the provided link Kristen, it really helped provide closure for this. |
|
|
|
madhivanan
Premature Yak Congratulator
India
20644 Posts |
Posted - 10/18/2007 : 06:31:32
|
Also, if you want to delete all the rows in a table then truncate it
Madhivanan
Failing to plan is Planning to fail |
|
|
|
eyancey
Starting Member
1 Posts |
Posted - 11/07/2007 : 09:47:24
|
I was looking at the ROWCOUNT documentation and BOL states:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.
Something to keep in mind if your scripts will be run against sql 2008 |
|
|
|
Kristen
Test
United Kingdom
19683 Posts |
Posted - 11/07/2007 : 09:55:31
|
As per the link I posted my preferred solution is to get all the PKs into a temp table and then delete in batches based on the Temp Table, so deprecation of SET ROCOUNT wouldn't effect that approach ... and DELETE TOP will do the same job of course (and much better since there won't be the potential side effect of Triggers only doing half a job!!)
Kristen |
|
|
|
X002548
Not Just a Number
USA
12780 Posts |
|
| |
Topic |
|
Deleting Large Number of Rows
