Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008
    Posts
    4

    Unanswered: How to cleanup a huge table?

    Hi,
    I'm trying to trim down a huge table in sybase database. We have around 25 million rows in the table from 1997. We want to keep the most recent three years and delete the rest. The thing is we cannot shut down the database and it should remain functional during the process.

    I tried doing a DELETE FROM TABLE WHERE DATE < 'date'. I had to cancel the query due to it taking too long and nearly taking down the server.

    pleeeease heeeelp.

    Thanks in advance.
    Anita

  2. #2
    Join Date
    May 2006
    Posts
    42
    you will need to run it in parts.
    like delete records frm 1997 to 1998 tonite.
    further 1998-99 tomorrow nite.
    likewise

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Would this do it for you. I imagine it would take a day to finish but you could alter the 20000 value to suit your needs. At least this way you wouldn't need to baby sit the process. It'll just delete 20k records then sleep for a minute until finished. I don't think it would hold any locks.

    Code:
    declare @cnt int
    set rowcount 20000
    
    select @cnt = 0
    
    while @@rowcount > 0
    begin
           select "Done", @cnt
           select @cnt = @cnt + 20000
    
           wait for delay '00:01'
    
           delete TABLE
           where DATE < 'your cut off date'
    end
    
    set rowcount 0
    Mike

  4. #4
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    Assuming a brief period with the table unavailable, you may consider this. It's a variation of the usual way of bcp'ing out the data to be saved, truncate the table, drop indexes, bcp in only the good data and recreate the indexes.
    - Build a similar table, loaded with the data you wish to keep
    - Ban access to the table
    - Use sp_rename to swap table names. Now, the good table is the one you built with fewer data
    - Copy the latest inserts, those arrived after you built the table
    - Grant access again

    A warning about sp_rename: views, stored procedures and triggers will still refer to the old table, so they need to be droped and created if contain references to the table.

    Regards,
    Mariano Corral

    Quote Originally Posted by anitaarbabi
    Hi,
    I'm trying to trim down a huge table in sybase database. We have around 25 million rows in the table from 1997. We want to keep the most recent three years and delete the rest. The thing is we cannot shut down the database and it should remain functional during the process.

  5. #5
    Join Date
    Jun 2008
    Posts
    4
    Hi Mike,

    I don't get what your code does. Could you please explain a little more?

    Anita

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    The key statement in his code is "set rowcount":

    set rowcount:
    causes Adaptive Server to stop processing the query (select, insert, update, or delete) after the specified number of rows are affected. The number can be a numeric literal with no decimal point or a local variable of type integer. To turn this option off, use:
    set rowcount 0

    The code above deletes all your useless records, not in a single run, but in "batches" of 20.000 records.
    I have to say it is a smart choice

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I don't get what your code does. Could you please explain a little more?
    As explained above, set rowcount x just limits the number of rows affected by any sql. The little program just loops round and round deleting any data before your cut off date but limiting itself to only deleting 20k rows at a time. This means there will still be data before this date in the table when it loop round again. It will then wait for a minute to allow any other programs to do their stuff. It also shows a rough rowcount to show the progress. It will stop when there aren't any records left to delete before your cut off date.

    Mike

    PS1 You can change the 20k value and the wait value to make things faster or slower.
    PS2 You should dump the database before doing any major change like this.

Posting Permissions

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