SQL Server Performance

Fast way to Remove the rows

Discussion in 'General DBA Questions' started by danny123, Nov 30, 2006.

  1. danny123 New Member

    Hi There

    We have a table with almost millions of rows . Now as per business requirement we need to remove all the rows and left with only 100,000 rows. When i use regular delete command it takes like forever.
    Is there any other way to solve this.

    Thanks advance.
  2. thomas New Member

    Create a new table, insert into it only the rows you want to keep.
    truncate and drop the old table. Rename the new table to be your old one.

    Remembering to recreate all the required Primary Keys, Indexes, Foreign Key Constraints, etc etc etc
  3. danny123 New Member

    Thanks alot for solution .This worked a lil faster as anyway there are 100,000 rows.
    But just for curousity sake is not there any other way then creating new table and all stuff. Any delete or system funcion tht do it quickly.

    Thanks alot for support
  4. Chappy New Member

    is there an index on the fields in your WHERE clause ?

    Eg, if youre saying DELETE FROM MYTABLE WHERE MyDateField <= '2006/01/01', and the datefield has no index on it, the delete will be slower than it could be
  5. MohammedU New Member

    Other than Thomos solution is Chappy's but it takes long time as well as it will block all the users accessing this table... because sql will get the table level locks when you are delete 100 mill. rows.

    If you are not in hurry you can use delete statement but use while loop do delete 1000/5000 at a time.

    while 1 =1
    set rowcount 1000
    DELETE FROM MYTABLE WHERE MyDateField <= '2006/01/01'
    if @@rowcount = 0 break

    Mohammed U.
  6. danny123 New Member

    Chappy - The field we are using in Where clause have the Index but still Million rows is the main issue.

    MuhammedU - Using while loop is fine but can you please explain how it is useful if we breakdown the number of rows in smaller ones.

    Thanks All
  7. xiebo2010cx Member

    Recently I did some DELETE performance research,

    first query processor tried to find out where your targeted data rows are, per the SQL profiler results, this causes huge disk-reads if no appropriate index can be used for large table.

    secondly, sql server takes action to 'delete' your data rows, for heap and table with clustered index, the DELETE action is different.

    Bug explorer/finder/seeker/locator
  8. MohammedU New Member

    If you use while loop...1000 row at a time...sql gets exclusive locks on 1000 rows only not the entire table...so that you can avoid blocking...

    I use both methods delete the data from production based on the data and table usage.

    Mohammed U.
  9. FrankKalis Moderator

    I would go with Thomas' suggestion. This is not only the one likely to be the fastest, but also the one using the least reseources in terms of time and disk space.

    Frank Kalis
    Microsoft SQL Server MVP
  10. danny123 New Member

    Thanks everybody for all your suggestions.


Share This Page