Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    3

    Unanswered: speed up delete on big table

    I have a large table used for logging that's getting pretty big and I have been asked to prune it. I've tried to use a batched approach to ensure that the tran log doesn't grow out of control. I've dropped the only index, which is a clustered index that covers four columns including the Primary Key column. I have left the Primary Key non-clustered index in place. There are about four FK constraints with other tables using the primary key ID. I guess performance is impacted because of these FK's. The following query takes forever and would appreciate a few tips to get deletes like this done quicker...

    DECLARE @counter INT
    SET @counter = 10000000

    WHILE (@counter > 0)
    BEGIN
    BEGIN TRAN

    PRINT @counter

    DELETE FROM LogTable
    WHERE ID < @counter AND ID > (@counter - 1000)
    AND [LogMessage] = 'Log message'
    SET @counter = @counter - 1000

    COMMIT TRAN
    END

    Thanks,
    Clive

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Each batch will require a full table scan - that won't help matters. What was the CI and what is the NCI?

    BTW - your < wants to be <=, otherwise you'll miss an ID at each pass.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2008
    Posts
    3
    hello,

    I dropped the CI - it was covering 4 columns including the ID column. The NCI is the PK, which is the ID column.

    I just found that one of the related tables has about 70 million rows in it. It looks like I can just truncate that related table as it just contains some non-essential timestamp data.

    Clive

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just noticed- you are clearing out the entire table. Why not just drop it and recreate?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2008
    Posts
    3
    No, the query is just removing those rows that have some specific text in one of the columns - see the where clause. Unless I'm missing something?

    Clive

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i know deleting from an index adds overhead but I am curious whether an index covering ID and the log message column wouldn't speed things along. Just thinking out loud.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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