SQL Server Performance

Update compared to Insert

Discussion in 'T-SQL Performance Tuning for Developers' started by klepser, Nov 4, 2007.

  1. klepser New Member

    Does anyone know whether there is a large amount of overhead involved in doing a delete/insert rather than an update on a table, when you know the primary key of the data you wish to change?
    Thanks in advance
  2. anandchatterjee New Member

    Let me explain my understanding from your query!
    1. You have huge amount of data.
    2. You want to run the update statement on primary key.
    3. It is taking too long time...
    If my understanding is right!
    If you you are sure with your update statement which would not violate the primaky key constraint then drop the primary key and run the update statement and re-create it. I think, it would minimize the time slot. Please let me know your feedback.
  3. klepser New Member

    Close. I have a large table with large amounts of data being pumped in continually througout the day. The application that is pumping the data is has to check to see if the data is already there (as the primary key is based around the data to be inserted). If the data is already there is will delete then insert the data again, rather that just updating the record (everything but the primary key).
    I am interested to know whether the process of deleting then inserting records is slower than actually updating the record that already exists.
    Thanx
  4. martins New Member

    Hi,
    An update is nothing more than a delete followed by an insert, and therefore also the slowest type of operation to perform on a table. With that said I will advise you to rather update than do the delete and insert. Maybe your application is making provision for the fact that something might go wrong between the delete and the insert, but if it doesn't there might be potential data loss.
    I'm not too sure whether SQL Server will keep the primary key in the case of an update, but in my mind it should. The delete followed by an insert will cause the primary key to be deleted and inserted again, which might cause re-ordering of your data and therefore more execution time.
    Hope this helps.
  5. FrankKalis Moderator

    [quote user="klepser"]
    Does anyone know whether there is a large amount of overhead involved in doing a delete/insert rather than an update on a table, when you know the primary key of the data you wish to change?
    Thanks in advance
    [/quote]
    The overhead is that you are executing two separated statements against the table while an UPDATE is just one statement. Update operations - on the physical level - are not necessarily combined DELETE/INSERT operations. That depends on several factors, such as presence of triggers, replicated environment, etc... Mostly they are updates in-place, which means that just the differing bits and bytes are replaced. Without knowing exactly what you want this for, I would suggest going for the UPDATE operation.
  6. Madhivanan Moderator

    If you want to don an updates on both parent and child tables, then do update on parent table and do delete/insert on child table
  7. satya Moderator

    Delete/Insert involves writes to the disk, when you have inferior hardware then your query will be affected by poor performance too.
    Not only that you need to consider for TEMPDB & transaction log for such operations efficiency.

Share This Page