Delete taking too long
-
I have a table that has approx 10 million rows in it. I need to delete about 10 thousand rows at a time with a WHERE clause, and the delete takes over an hour to complete.
The column referenced in the WHERE clause is indexed. There are 2 other tables referenced by this table, but there are no rows in those tables referencing the parent rows.
Any ideas?
thanks
-
Are there many indexes on the table? The overhead of managing the indexes will significantly increase the processing time. You may consider dropping the indexes, performing the delete, and recreating the indexes.
This is a case where a cursor may be acceptable. I've used the code below to step through a table to perform an update, you may be able to hack this into something useful.
/*Begin Code*/
set nocount on
declare @i int, @batch_size int, @rowcount int, @last_number int
set @batch_size = 1000
set @rowcount = @batch_size
set @last_number = ''
WHILE @rowcount = @batch_size
BEGIN
--Put a list of the tables Primary Key into a temp table for rows to process this pass.
--Change the where clause to suit your needs
set rowcount @batch_size
select distinct Sequential_Number into #number_list
from dbo.yourtable
where Sequential_Number > @last_number
order by Sequential_Number
--Variable @rowcount will be used to break out of the loop.
set @rowcount = @@ROWCOUNT
set rowcount 0
set @last_number = (select MAX(Bill_Number) from #number_list)
--Perform your delete here.
delete dbo.yourtable
from dbo.yourtable a
join #number_list b on a.Sequential_Number = b.Sequential_Number
drop table #number_list
--end while
END
/*Begin Code*/
-
When you say that two table reference your 10 million row table, do you mean that there is a logical relationship or a physical i.e. foreign key constraints enforcing the relationship?
-
I have a physical FK constraint on there.
thanks
-
If you are absolutely positive that the records that you are about to delete don't have an related records in other tables then, as part of the delete process, try dropping the constraint before you do the delete then re-enable the constraint afterwards.
-
Just be sure that there are no other operations that are going to be requiring that constraint running whilst it is dropped.
Tony
-
What does
Explain PlanShow Execution Plan show?Sorry, Explain Plan is a common term and I've heard it used with SQL Server too, but mostly by ex-Oracle DBAs.
-
Explain Plan?
This is still SQL Server, not MySQL, right
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
As Brian and Tony pointed out, try disabling this constraint before deleting the records. Another way maybe to delete the records in batches of say 500 or 1000. Check it out if that still fulfills your requirement.
Cheers!
Arvind
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply