In SQL is UPDATE always faster than DELETE INSERT

Question

Say I have a simple table that has the following fields    ID  int  autoincremental  identity   primary key Name  varchar 50   unique  has unique index Tag  int   I never use the ID field for lookup  because my application is always based on working with the Name field    I need to change the Tag value from time to time  I m using the following trivial SQL code    UPDATE Table SET Tag   XX WHERE Name   YY    I wondered if anyone knows whether the above is always faster than   DELETE FROM Table WHERE Name   YY  INSERT INTO Table  Name  Tag  VALUES  YY  XX     Again - I know that in the second example the ID is changed  but it does not matter for my application

User · Answer

Large number of individual updates vs bulk delete/bulk insert is my scenario.I have historical sales data for multiple customers going back years. Until I get verified data (15th of the following month), I will adjust sales numbers every day to reflect the current state as obtained from another source (this means overwriting at most 45 days of sales each day for each customer). There may be no changes, or there may be a few changes. I can either code the logic to find the differences and update/delete/insert the affected records or I can just blow away yesterday's numbers and insert today's numbers. Clearly this latter approach is simpler, but if it's going to kill the table's performance due to churn, then it's worth it to write the extra logic to identify the handful (or none) of records that changed and only update/delete/insert those.

So, I'm replacing the records, and there may be some relationship between the old records and the new records, but in general I don't necessarily want to match the old data with the new data (that would be an extra step and would result in deletions, updates, and inserts). Also, relatively few fields would be changed (at most 7 out of 20 or 2 out of 15).

The records that are likely to be retrieved together will have been inserted at the same time and therefore should be physically close to each other. Does that make up for the performance loss due to churn with that approach, and is it better than the undo/redo cost of all those individual record updates?

User · Answer

In specific cases  Delete Insert would save you time  I have a table that has 30000 odd rows and there is a daily update insert of these records using a data file  The upload process generates 95  of update statements as the records are already there and 5  of inserts for ones that do not exist  Alternatively  uploading the data file records into a temp table  deletion of the destination table for records in the temp table followed by insertion of the same from the temp table has shown 50  gain in time

User · Answer

Just tried updating 43 fields on a table with 44 fields  the remaining field was the primary clustered key   The update took 8 seconds   A Delete   Insert is faster than the minimum time interval that the  Client Statistics  reports via SQL Management Studio   Peter  MS SQL 2008

User · Answer

What if you have a few million rows   Each row starts with one piece of data  perhaps a client name   As you collect data for clients  their entries must be updated   Now  let s assume that the collection of client data is distributed across numerous other machines from which it is later collected and put into the database   If each client has unique information  then you would not be able to perform a bulk update  i e   there is no where-clause criteria for you to use to update multiple clients in one shot   On the other hand  you could perform bulk inserts   So  the question might be better posed as follows   Is it better to perform millions of single updates  or is it better to compile them into large bulk deletes and inserts   In other words  instead of  update  table  set field data where clientid 123  a milltion times  you do  delete from  table  where clientid in   all clients to be updated   insert into  table  values  data for client1    data for client2   etc   Is either choice better than the other  or are you screwed both ways

User · Answer

Obviously  the answer varies based on what database you are using  but UPDATE can always be implemented faster than DELETE INSERT   Since in-memory ops are mostly trivial anyways  given a hard-drive based database  an UPDATE can change a database field in-place on the hdd  while a delete would remove a row  leaving an empty space   and insert a new row  perhaps to the end of the table  again  it s all in the implementation    The other  minor  issue is that when you UPDATE a single variable in a single row  the other columns in that row remain the same   If you DELETE and then do an INSERT  you run the risk of forgetting about other columns and consequently leaving them behind  in which case you would have to do a SELECT before your DELETE to temporarily store your other columns before writing them back with INSERT

User · Answer

Delete   Insert is almost always faster because an Update has way more steps involved   Update    Look for the row using PK  Read the row from disk  Check for which values have changed Raise the onUpdate Trigger with populated  NEW and  OLD variables Write New variables to disk  The entire row    This repeats  for every row you re updating    Delete   Insert     Mark rows as deleted  Only in the PK   Insert new rows at the end of the table  Update PK Index with locations of new records    This doesn t repeat  all can be perfomed in a single block of operation     Using Insert   Delete will fragment your File System  but not that fast  Doing a lazy optimization on the background will allways free unused blocks and pack the table altogether

User · Answer

Every write to the database has lots of potential side effects   Delete  a row must be removed  indexes updated  foreign keys checked and possibly cascade-deleted  etc  Insert  a row must be allocated - this might be in place of a deleted row  might not be  indexes must be updated  foreign keys checked  etc  Update  one or more values must be updated  perhaps the row s data no longer fits into that block of the database so more space must be allocated  which may cascade into multiple blocks being re-written  or lead to fragmented blocks  if the value has foreign key constraints they must be checked  etc   For a very small number of columns or if the whole row is updated Delete insert might be faster  but the FK constraint problem is a big one   Sure  maybe you have no FK constraints now  but will that always be true  And if you have a trigger it s easier to write code that handles updates if the update operation is truly an update   Another issue to think about is that sometimes inserting and deleting hold different locks than updating   The DB might lock the entire table while you are inserting or deleting  as opposed to just locking a single record while you are updating that record   In the end  I d suggest just updating a record if you mean to update it   Then check your DB s performance statistics and the statistics for that table to see if there are performance improvements to be made   Anything else is premature   An example from the ecommerce system I work on    We were storing credit-card transaction data in the database in a two-step approach  first  write a partial transaction to indicate that we ve started the process   Then  when the authorization data is returned from the bank update the record   We COULD have deleted then re-inserted the record but instead we just used update   Our DBA told us that the table was fragmented because the DB was only allocating a small amount of space for each row  and the update caused block-chaining since it added a lot of data   However  rather than switch to DELETE INSERT we just tuned the database to always allocate the whole row  this means the update could use the pre-allocated empty space with no problems   No code change required  and the code remains simple and easy to understand

User · Answer

The question of speed is irrelevant without a specific speed problem   If you are writing SQL code to make a change to an existing row  you UPDATE it   Anything else is incorrect   If you re going to break the rules of how code should work  then you d better have a damn good  quantified reason for it  and not a vague idea of  This way is faster   when you don t have any idea what  faster  is

User · Answer

It depends on the product  A product could be implemented that  under the covers  converts all UPDATEs into a  transactionally wrapped  DELETE and INSERT  Provided the results are consistent with the UPDATE semantics   I m not saying I m aware of any product that does this  but it s perfectly legal

User · Answer

I am afraid the body of your question is unrelated to title question     If to answer the title        In SQL  is UPDATE always faster than DELETE INSERT      then answer is NO   Just google for      Expensive direct update    sql server      deferred update    sql server      Such update s  result in more costly  more processing  realization of update through insert update than direct insert update   These are the cases when     one updates the field with unique  or primary  key or  when the new data does not fit  is bigger  in the pre-update row space allocated  or even maximum row size  resulting in fragmentation   etc        My fast  non-exhaustive  search  not pretending to be covering one  gave me  1    2      1  Update Operations  Sybase   SQL Server Performance and Tuning Guide Chapter 7  The SQL Server Query Optimizer  http   www lcard ru  nail sybase perf 11500 htm  2  UPDATE Statements May be Replicated as DELETE INSERT Pairs http   support microsoft com kb 238254

User · Answer

A bit too late with this answer  but since I faced a similar question  I made a test with JMeter and a MySQL server on same machine  where I have used    A transaction Controller  generating parent sample  that contained two JDBC Requests  a Delete and an Insert statement A sepparate JDBC Request containing the Update statement    After running the test for 500 loops   I have obtained the following results   DEL   INSERT - Average  62ms  Update - Average  30ms  Results

User · Answer

In your case  I believe the update will be faster   Remember indexes   You have defined a primary key  it will likely automatically become a clustered index  at least SQL Server does so   A cluster index means the records are physically laid on the disk according to the index  DELETE operation itself won t cause much trouble  even after one record goes away  the index stays correct  But when you INSERT a new record  the DB engine will have to put this record in the correct location which under circumstances will cause some  reshuffling  of the old records to  make place  for a new one  There where it will slow down the operation   An index  especially clustered  works best if the values are ever increasing  so the new records just get appended to the tail  Maybe you can add an extra INT IDENTITY column to become a clustered index  this will simplify insert operations

User · Answer

One command on the same row should always be faster than two on that same row   So the UPDATE only would be better   EDIT set up the table   create table YourTable  YourName  varchar 50   primary key  Tag int    insert into YourTable values   first value  1    run this  which takes 1 second on my system  sql server 2005    SET NOCOUNT ON declare  x int declare  y int select  x 0  y 0 UPDATE YourTable set YourName  new name  while  x lt 10000 begin     Set  x  x 1     update YourTable set YourName  new name  where YourName  new name      SET  y  y   ROWCOUNT end print  y   run this  which took 2 seconds on my system   SET NOCOUNT ON declare  x int declare  y int select  x 0  y 0 while  x lt 10000 begin     Set  x  x 1     DELETE YourTable WHERE YourName  new name      insert into YourTable values   new name  1      SET  y  y   ROWCOUNT end print  y

User · Answer

Keep in mind the actual fragmentation that occurs when DELETE INSERT is issued opposed to a correctly implemented UPDATE will make great difference by time   Thats why  for instance  REPLACE INTO that MySQL implements is discouraged as opposed to using the INSERT INTO     ON DUPLICATE KEY UPDATE     syntax

User · Answer

The bigger the table  number of and size of columns  the more expensive it becomes to delete and insert rather than update   Because you have to pay the price of UNDO and REDO   DELETEs consume more UNDO space than UPDATEs  and your REDO contains twice as many statements as are necessary     Besides  it is plain wrong from a business point of view   Consider how much harder it would be to understand a notional audit trail on that table       There are some scenarios involving bulk updates of all the rows in a table where it is faster to create a new table using CTAS from the old table  applying the update in the the projection of the SELECT clause   dropping the old table and renaming the new table  The side-effects are creating indexes  managing constraints and renewing privileges  but it is worth considering

[sql] In SQL, is UPDATE always faster than DELETE+INSERT?

Examples related to sql

Examples related to sql-insert

Examples related to sql-delete