Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Location
    Sweden
    Posts
    15

    Angry Unanswered: deleting 11 000 000 rows...

    Hi!
    I just dicovered that one of our tables contains over 11 000 000 rows.
    My problem right now is to delete a major part of these rows.
    What I've done so far is to create a sp that handles this, but no matter how I do, the batch gets sent with all delete:s at the same time.

    What I understand, the batch gets sent after the GO-statement, and at the same time, all my variables looses scope...



    The data is organized on dates, one date may comprise several rows, and I know that one batch can handle all rows in one date...

    The table is indexed but has no PK


    The main SP:

    CREATE PROCEDURE clear_lagertransaktioner
    @maxDate SMALLDATETIME
    AS
    DECLARE @date SMALLDATETIME
    DECLARE dateCur CURSOR FOR select distinct date from lagertransaktioner
    OPEN dateCur

    FETCH NEXT FROM dateCur INTO @date
    WHILE @@FETCH_STATUS = 0 AND @date<@maxDate
    BEGIN

    --Call another SP that I hoped would send the batch
    exec del_post_lagertransaktioner @date
    FETCH NEXT FROM dateCur INTO @date

    END

    CLOSE dateCur
    DEALLOCATE dateCur
    GO






    The nested SP:
    CREATE PROCEDURE del_post_lagertransaktioner
    @d SMALLDATETIME
    AS

    DELETE FROM lagertransaktioner WHERE date=@d
    GO




    Does any of you have a better idea, cause this doesn't work.

  2. #2
    Join Date
    Mar 2003
    Location
    Sweden
    Posts
    15

    Me again...

    Mabye it is better for me to just insert needed data into a new table and drop the original table, since this is a once-in-a-lifetime situation (hopefully)

    What do you say?

    /Bix

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Two options.

    Run SELECT INTO and grab your small amount of rows and insert them into a new table. Then DROP the old table and re-name the new one. Provided you have an index to select from that should provide the lowest I/O.

    If you want to batch it, do something like this:
    Code:
    SET ROWCOUNT 10000
    WHILE @@ROWCOUNT > 0
      DELETE FROM table_name WHERE etc.
    That'll delete 10,000 of them at a time, for example.

    You could also re-write the DELETE to use a subquery that uses TOP 10000 to also achive this effect.
    Thanks,

    Matt

Posting Permissions

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