SQL Server Performance

Delete statement Tunning

Discussion in 'SQL Server 2005 General DBA Questions' started by dhamu294, Mar 8, 2011.

  1. dhamu294 New Member

    Hi,
    Below statement takes more than hour to delete a million of records. what will be the optimized way for delete.
    Can i delete without top statement ?
    can i remove index and recreate after delete statement. ?
    Please give some optimised solutions
    Thanks in adavnce
    DELETE TOP (5000) tableA aFROM tableA a, TableB b
    WHERE a.cond1= b. cond1AND b.col2 = 0
    AND b.col3 = 0WHILE
    @@rowcount > 0 DELETE TOP (5000) tableA a
    FROM tableA a, TableB
    WHERE a.cond1= b. cond1 AND b.col2 = 0
    AND b.col3 = 0
  2. FrankKalis Moderator

    The DELETE statement looks ok to me. Not much you can do about it.
    Is there anything else going on in that tables during the DELETE?
    Which index do you mean? Do you have indexes on both table to support this statement?
  3. satya Moderator

    Do you need to run the DELETE operation regularly or this is one-off?
    ALso what is the rowcount on the table where you are deleting nearly 1 Million rows?
  4. Kaarthikeyan_S New Member

    In the first place "Deleting Millions of records from a table is a crime". But still if you have such a scenario, then should I assume that the table is offline and not into production. In this case you can move only those records that you would want to retain after the deletion operation you want to perform, to a new table. Truncate you table and insert back the records back to the original table. Even if in case the table is live then deletion will kick the table out of production and all indexes will be going for a toss.God bless in either case :)
  5. FrankKalis Moderator

    [quote user="Kaarthikeyan_S"]In the first place "Deleting Millions of records from a table is a crime". But still if you have such a scenario, then should I assume that the table is offline and not into production. In this case you can move only those records that you would want to retain after the deletion operation you want to perform, to a new table. Truncate you table and insert back the records back to the original table. Even if in case the table is live then deletion will kick the table out of production and all indexes will be going for a toss.God bless in either case :)[/quote]
    Not sure I agree with you. I have a table with +1 billion rows from which we delete a few million rows each night. If the delete is done "correctly" in batches (with maybe some time for the server to breathe in between these batches), it seems okay to me. I certainly wouldn't want to copy over the vast majority of rows into a new table just to get rid of 2-3 % of the data. [:)]
  6. satya Moderator

    [quote user="Kaarthikeyan_S"]In the first place "Deleting Millions of records from a table is a crime". But still if you have such a scenario, then should I assume that the table is offline and not into production......... God bless in either case :)[/quote]
    I like these statements and agree with comments [:)], in any case you must build up a controlled process to delete such a size of data.
  7. mmarovic Active Member

    There are a few solutions that can be applied but it depends a lot of your where condition, indexes, the number of rows in the table and the number of rows matching each condition in your where clause.Sometimes you delete in batches by joining Ids to be deleted with number table starting with min id that matches the criteria until max id is reached. Sometimes you insert ids into temp or auxiliary table with identity column and use again condition: identity column between @start and @start + @batch_size - 1 etc...
  8. Luis Martin Moderator

    "can i remove index and recreate after delete statement. ?"
    Yes you can. If you can run your script in window time, I suggest that.

Share This Page