What's better for large changes to a table: DELETE and INSERT every time or UPDATE existing?

Question

I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better:

1. delete rows and insert new ones, or
2. update already existing rows

For me it is easier to just delete all the rows and insert new ones, but if this is going to fragment the table and indexes and impact performance then I would prefer to make updates where possible and delete/insert only when necessary.

This is going to be a nightly service and I am not looking to improve the speed of the process itself. I am more concerned about the performance of queries against this table in general where I already have 89 million records and how this nightly process will affect it.

Should I delete/insert records or should I update existing ones (where possible) for this nightly process?

Answer 1

It really depends on how much of the data is changing. Lets say this table has 20 columns. And you also have 5 indexes - each on a diff. column.

Now if the values in all 20 columns are changing OR even if data in 5 columns are changing and these 5 columns are all indexed, then you may be better off "deleting and inserting". But if only 2 columns are changing and lets say these are not part of any non-clustered indexes, then you may be better off "Updating" the records because in this case only the clustered index will be updated (and indexes will not have to be updated).

---

On further research, I did find that the above comment by me is sort of redundant as SQL Server internally has 2 separate mechanism for performing an UPDATE. - An "in-place update" (ie by changing a columns value to a new in the original row) or as a "not-in-place UPDATE" (DELETE followed by an INSERT).

In place updates are the rule and are performed if possible. Here the rows stay exactly at the same location on the same page in the same extent. Only the bytes affected are chnaged. The tlog only has one record (provided there are no update triggers). Updates happen in place if a heap is being updated (and there is enough space on the page). Updates also happen in place if the clustering key changes but the row does not need to move at all.

For eg: if you have a clustered index on last name and you have the names: Able, Baker, Charlie Now you want to update Baker to Becker. No rows have to be moved. So this can take in-place. Whereas, if you have to update Able to Kumar, the rows will have to be shifted (even though they will be on the same page). In this case, SQL Server will do a DELETE followed by an INSERT.

Considering the above, I would suggest that you do a normal UPDATE and let SQL Server figure out the best way to how to do it internally.

For more details on "UPDATE" internals or for that matter any SQL Server related internals, check out Kalen Delaney, Paul Randal's, et al.'s book - SQL Server 2008 Internals.

Answer 2

Have you investigated the MERGE command in SQL 2008? Here is a basic example:

  merge YourBigTable ybt
  using (select distinct (RecordID) from YourOtherTable) yot
     on yot.Recordid = YBT.RecordID
  when NOT matched by target
  then  insert (RecordID)
        values (yot.DeviceID) ;

This is basically an "UPSERT" command. Update if it exists, insert it if it does not. VERY fast, very cool command.

Answer 3

But, I myself checked the Delete and Insert vs Update on a table that has 30million (3crore) records. This table has one clustered unique composite key and 3 Nonclustered keys. For Delete & Insert, it took 9 min. For Update it took 55 min. There is only one column that was updated in each row.

So, I request you people to not guess. The equations will change when dealing with large table with many columns and with much data.

Answer 4

Update is not as fast. The trick is to achieve a fast insert is to disable the indexes while data is being inserted.

Consider using this:

-- disable indexes
ALTER INDEX [index_name] ON dbo.import_table DISABLE
-- ... disable more indexes

-- don't use delete if you don't care about minimal logging. truncate is faster
TRUNCATE TABLE dbo.import_table

-- just insert the new rows
INSERT dbo.import_table
SELECT
    *
FROM
    dbo.source_table

-- rebuild indexes
ALTER INDEX [index_name] ON dbo.import_table REBUILD
-- ... rebuild more indexes

Even faster is to also turn off automatic statistics update in the db options. If the table is significantly changed you should run:

UPDATE STATISTICS dbo.import_table

or

EXEC sp_updatestats

as a job on a regular basis (daily, weekly depending on db size) to keep the stats up to date. On thing to look out for is to update statistics when the table is empty. That will screw up the stats if you don't run it after the table has been populated again.