SQL Server Performance

Delete or Update - SQL Server 2012

Discussion in 'ALL SQL SERVER QUESTIONS' started by neerajmalik, Nov 4, 2013.

  1. neerajmalik New Member

    Hi there,

    We are working on a medium size table which will have more than 100 million records. And the data will be loaded each hour with sql job and it will be constantly queried by reporting system. During the load about 10,00 rows will be deleted with each run and alot more will be inserted.

    Now, which way is better to go:
    1. Delete the 1000 rows each hour?
    2. Put a soft delete flag (basically and update statement) and then actually delete the records off peak hours?

    Please suggest!

    Thanks in advance!
  2. davidfarr Member

    It is probably better to delete the 1000 records each hour. 1000 records is not a lot of data and so the delete operation (in terms of data volume and time) will not be noticeably slower or heavier than an updated flag column. The duration and frequency of locks on the table from the delete operation will also be very similar to a flag column update operation. If it was 10000 records or more then I would recommend the soft delete flag column instead.

    Your challenge is to keep the index from fragmenting, because a fragmented index would make both a delete statement and an update statement take longer (if they contain a 'WHERE' clause) and it will hold a lock on those table pages during the query, which could suspend some reporting queries.
    If you are constantly removing 1000 records and adding another few thousand records or more each hour then your index could start to fragment by the end of each day, and you should probably run a re-indexing or defrag maintenance job on off-peak hours each night, or at least each week.
  3. AJITH123 Member

    Update is nothing but delete insert...the concern is index fragmentation. Think bit on table partition, may help to reindex a specific partition?
  4. rahul.neekhra New Member

    Hi Neeraj,

    I would recommend some more tricky solution but that will give you peace in long run.

    Do the partition of the table based on date and drop the complete partition in less peak hours or off hours.
    The advantage of date based partition is that your reporting can use these partitions for their select queries for the given date range.

    For that your SQL Server Version must support the table partition.

    For any other help you can send me a mail @ rahul.neekhra@sqlserverpeoples.com

    Thanks & regards,
    Rahul Neekhra
    Blog: www.sqlserverpeoples.com

Share This Page