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?



    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


    --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



    /*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.


  • 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.


  • What does Explain Plan Show 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
    My blog:[/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.



Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply