Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    May 2002
    Posts
    16

    Unanswered: How to delete rows from a 15 million row table

    Using SQL 2000, I'm trying to trim down a 15 million row table. I initially tried simply doing a DELETE FROM TABLE WHERE DATE < '10/04/2002'. The initial start date for the table was in august so I figured this would account for about 1/3 of its contents, assuming an even daily amount was being inserted.

    However, this query would need to go through the entire table to successfully perform and I had to cancel the query due to it taking too long and nearly taking down the sql server due to all the hd crunching.

    Then I tried deleting it in batches...by using set rowcount = 10000, then sticking that delete statement into a nested loop and having it issue a checkpoint whenever it found a row to delete. This too, took forever just even through the first pass. Using Profiler, I watched the query get to the delete statement, then take forever again...and again I had to cancel it.

    Is there a simpler way to do this, or am I missing something? Why would it take forever even for the first pass I would really like something that would export or backup the data first, then delete it.

    Ed

  2. #2
    Join Date
    Jan 2003
    Posts
    5

    Re: How to delete rows from a 15 million row table

    Hi,
    y dont u try splitting ur 15 million row table into some 10 or 15 temporary tables, delete the records in the temporary tables and then group it back to the main table??,the Split-table approach??
    Regards,
    Ramya

    Originally posted by KungFuJoe
    Using SQL 2000, I'm trying to trim down a 15 million row table. I initially tried simply doing a DELETE FROM TABLE WHERE DATE < '10/04/2002'. The initial start date for the table was in august so I figured this would account for about 1/3 of its contents, assuming an even daily amount was being inserted.

    However, this query would need to go through the entire table to successfully perform and I had to cancel the query due to it taking too long and nearly taking down the sql server due to all the hd crunching.

    Then I tried deleting it in batches...by using set rowcount = 10000, then sticking that delete statement into a nested loop and having it issue a checkpoint whenever it found a row to delete. This too, took forever just even through the first pass. Using Profiler, I watched the query get to the delete statement, then take forever again...and again I had to cancel it.

    Is there a simpler way to do this, or am I missing something? Why would it take forever even for the first pass I would really like something that would export or backup the data first, then delete it.

    Ed

  3. #3
    Join Date
    Jan 2003
    Posts
    14
    I had a similar problem on an older server. There I had not enough space to make a copy of the data i had to keep on the database. So I made a stored procedure which went through a cursor that deleted 10000 rows. Then I packed the procedure into an SQL-Task in a DTS, which i scheduled for running several times in the night.

  4. #4
    Join Date
    May 2002
    Posts
    16

    Re: How to delete rows from a 15 million row table

    I can't risk bringing this table down for any amount of time...this is mission critical 24/7 database that the table resides on. I need something that will work while the database is still up.

    Ed



    Originally posted by ramya
    Hi,
    y dont u try splitting ur 15 million row table into some 10 or 15 temporary tables, delete the records in the temporary tables and then group it back to the main table??,the Split-table approach??
    Regards,
    Ramya

  5. #5
    Join Date
    May 2002
    Posts
    16
    Do you have this stored procedure handy so I can look at it? I tried doing something similar (using set rowcount = ) as I described in my initial post, but I let it run for nearly 3 hours and it did not delete a single row.

    Ed




    Originally posted by austrian_ead
    I had a similar problem on an older server. There I had not enough space to make a copy of the data i had to keep on the database. So I made a stored procedure which went through a cursor that deleted 10000 rows. Then I packed the procedure into an SQL-Task in a DTS, which i scheduled for running several times in the night.

  6. #6
    Join Date
    Jan 2003
    Posts
    14
    Originally posted by KungFuJoe
    Do you have this stored procedure handy so I can look at it? I tried doing something similar (using set rowcount = ) as I described in my initial post, but I let it run for nearly 3 hours and it did not delete a single row.

    Ed
    I am sorry, I don't have it anymore, but the code was not complicated:

    declare @columnx integer
    declare cur_test cursor for
    select top 10000 0 as columnx from bigtable
    for update
    open cur_test
    fetch cur_test into @columnx
    while @@fetch_status = 0
    begin
    delete from bigtable where current of cur_test
    fetch cur_test into @columnx
    end
    close cur_test
    deallocate cur_test

    it's like this, not exactly, it's only a draft. You should try it it with a small database for testing.

  7. #7
    Join Date
    May 2002
    Posts
    16
    Thanks for your help

    I'll give it a shot.

    Ed


    Originally posted by austrian_ead
    I am sorry, I don't have it anymore, but the code was not complicated:

    declare @columnx integer
    declare cur_test cursor for
    select top 10000 0 as columnx from bigtable
    for update
    open cur_test
    fetch cur_test into @columnx
    while @@fetch_status = 0
    begin
    delete from bigtable where current of cur_test
    fetch cur_test into @columnx
    end
    close cur_test
    deallocate cur_test

    it's like this, not exactly, it's only a draft. You should try it it with a small database for testing.

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    what's the secret there? bcp out the records you want to keep, alter table tbl nocheck constraint all, truncate, bcp in!

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, but he says the table is used 24/7 and it can't be down at all. It's heavy use is probably why the deletes are taking so long.

    Maybe he should try turning off loging, deleting his records, and then turning it back on again and starting a new backup cycle.

    Otherwise, I think his best bet is to keep deleteing them in small batches, and as separate transactions.

    I don't see what he gets out of using a cursor for this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: How to delete rows from a 15 million row table

    Originally posted by KungFuJoe
    I can't risk bringing this table down for any amount of time...this is mission critical 24/7 database that the table resides on. I need something that will work while the database is still up.
    Ed
    What is that there is no maint window?

    Also, is there an index on the column?

    Have you checked out sp_lock? what processes are going on?

    Is this a web based app?

    And you say 3 months is a 1/3 of the data...that's a lot of data AND I'm sure it's going to continue to grow...

    You seriously need to understand the growth of this monster and plan an archival strategy...what type of data btw is ancient history after that period of time....don't you track a status or something...or is like phone records?

    what does your app do?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually, my understanding is that the database needs to be 24/7, not the table. Besides, the poster already mentioned making several attempts to delete from this table, during which it WAS inaccessible for anything other than SELECT with READ_UNCOMMITTED isolation level.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: How to delete rows from a 15 million row table

    Originally posted by KungFuJoe
    I can't risk bringing this table down for any amount of time...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I can't risk bringing this table down for any amount of time...this is mission critical 24/7 database that the table resides on . I need something that will work while the database is still up.

    Ed

  14. #14
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Lightbulb

    Well if it isn't the SQL Server hardcore !!!

    How you dudes doing ???

    Here is a novel way of removing records from a HUGE table.

    You could turn Select Into / Bulk Copy on

    Perform a select into non-logged operation using a where clause to filter the records into another table. If you have an index on the column with the where clause on it then should be fast.

    reindex the new table.

    Perfom a two sp_renames of the two tables.

    If you are prepared to have you db down for the time it takes perform the two sp_renames then you are jammin'!

    Got to love those non - logged op's.

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Here we go again...and how this better? You forgot about recompiles that you'll have to do...got enough time to spare?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •