Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Hey adopilot, I edited your question so that it's clearer. Does it look better to you? –  Nick Chammas Nov 17 '11 at 14:52
    
@NickChammas Thanx nick, My apology on bad English, I am not from English speaking area, and by the way I get around 30 phone calls per hour. –  adopilot Nov 17 '11 at 14:56
3  
No worries. A lot of us here on the site enjoy editing posts to improve them. And it sounds like you need a secretary! Or maybe you should just turn off your phone. :) –  Nick Chammas Nov 17 '11 at 15:12
    
I believe you should give more details on your table, as I guess it would depend on the potential existence of indices on fields. –  SRKX Nov 17 '11 at 19:49
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
It isn't faster than an UPDATE, same mechanics under the hood. –  Mark Storey-Smith Nov 18 '11 at 17:44
    
It is faster than updating then inserting the ones that did not already exist. –  datagod Nov 18 '11 at 18:06
1  
If you know that's the case, prove it :) –  Mark Storey-Smith Nov 18 '11 at 23:05
add comment

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.

share|improve this answer
1  
I disagree that this is always the case. Also, the table in @adopilot's question cannot be cleared by TRUNCATE as it contains 89m records and he wants to update only 36k. –  Mark Storey-Smith Nov 18 '11 at 15:32
    
need to learn to read the post more careful! i'll update the post... actually, i need to change a lot. –  Asken Nov 18 '11 at 15:37
1  
We've all done it :) –  Mark Storey-Smith Nov 18 '11 at 15:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.