Deletes take forever
-
I have a table in which i need to delete huge amount of rows very frequently in the order of 200,000.
So my deletes take forever to commit. Is there a way to delete without logging? truncate table is not an option as i dont want to delete all the rows in the table.
Any suggestion will be of great help.
Thanks a bunch
-
What is the Ratio RowsStaying/TotalRows ?
* Noel -
Total # of rows 5306052
Rows to be deleted 175000
-
I just ran DBCC SHOWCONTIG on the table and found out that the indexes might be fragmented.
So I will give DBCC Reindex a shot and see how it affects the "Deletes"
-
You can turn off logging by changing the database recovery model to 'simple', using ALTER DATABASE command.
Not recommended during online processing window, as it affects all other processes.
-
Are there foreign keys that reference this table? Deletes have to check each child table to ensure that foreign key relationships are maintained (by restricting or cascading the delete). I have found that deletes from tables that have lots of foriegn keys can take quite a long time to do these checks.
Scott Thornburg
Volt Information Sciences
-
Since 200k rows are really not much at all, what means "takes forever" to you? What is the DDL for that table. Indexes... What does your statement look like?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
my statement looks like this
delete from table where column = 'value'
-
>>You can turn off logging by changing the database recovery model to 'simple', using ALTER DATABASE command.<<
That is not true, you can't turn logging off on SQL Server. The Simple recovery mode just truncates the log at checkpoint time but the logging opperation is still there!
1. If you can afford to Delete in one single opperation make sure you have transaction log size to avoid log expansion
2. If you can do it in chuncks then it will be appropriate to select say 2000 or so at a time and make sure the amount of locking is acceptable
3. There is also as Scott Thornburg mentioned, the posibility of Foreign Keys AND Triggers affecting the operation, so if it is possible to Disable them before and enable them after then you should consider to do so.
4. The Physical Aspect (Disk Hardware) will start to get into place after your table grows just a bit more but you should plan for it, if you have the budget
HTH
* Noel -
How about Bulk Copying the rows that you want to keep into a new table, then drop the old, rename the new, and run a script to recreate the relationships/triggers/etc. ?
-
I've used 'set rowcount 10000' on more than one occasion. It doesn't do much for speeding things up, but it at least lets the process come up for a breath of fresh air and lets other processes get some processor time in.
set rowcount 10000
declare @last_rowcount int
set @last_rowcount = 10000
while @last_rowcount = 10000
begin
delete from table where column = 'something'
set @last_rowcount = @@rowcount
end
set rowcount 0
Don't forget your grain of salt. I'm a newbie.
-
It could also be related to a clustered index. It looks like you have nearly 6 million records, and you're deleting roughly 200 thousand records. Depending on the clustered index, you may be incurring a lot of reorganizing during the delete process. (Clustered indexes physically store data in order).
If this table is not heavily indexed, and you are doing this during off-hours, you may find it easier and faster to:
1) drop all indexes and clustered index on the table.
2) create a non-clustered index that best matches your delete operation
3) run the deletes
4) drop the non-clustered index.
5) re-create your clustered index and other indexes.
-
Related to the foreign key suggestion - do you have indexes on the foreign key columns in the other tables? This will speed the lookup significantly.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply