Is it faster to UPDATE a row, or to DELETE it and INSERT a new one?

Question

Given two scenarios on SQL Server 2008/2005 - 1 Table has 5 rows 2 Table has 1 million rows

If we need to update a few rows, what is is efficient and why? 1) UPDATE the required columns 2) DELETE the row and INSERT new row with the updated information

Solution 1

You should not be asking this question. You are asking "Is it better to do it the right way, or the wrong way, in the name of some nebulous idea of 'faster'?"

Do you have an application that is somehow too slow? Do you for some reason think that the problem is because your UPDATEs are taking too long? Have you done any measurement and benchmarking of the performance of your database interactions?

What you are doing is premature optimization of the worst kind, and you are doing your application a disservice by doing so. You are making wild guesses about how to speed up your code, with absolutely nothing to base it on.

Write your code right. Then try to find where you have a performance problem. Do you even HAVE a performance problem, or are you asking this question simply because you think it's something you should be asking about? You shouldn't.

Even if you specifically DID have a problem with your UPDATEs being too slow, we can't answer the question of "Is X faster than Y" because you have not given us nearly enough information, such as:

• What database you are using
• The table layouts
• What indexes are on the database
• How you're interfacing with the database

Please, write your code correctly, and then come back with specifics about what is too slow, rather than guessing at micro-optimizations.

Solution 2

Usually updating a single row will be faster. Reason being deleting existing row and inserting a new row, both of these operations will impact clustered index. Updating a single row will also have impact on various indices but not on clustered index. No data point to support my claim but logically DB engines should behave this way.

Comments

[Saurabh Kumar]

Points well taken. Well, this question was one of the questions I was asked in an interview at a company. I myself was taken aback at this unexpected question and I gave them my thinking and reasons. Wanted to share the question here, in case I uncover any new dimensions which I might have been overlooking earlier. Thanks for your time on this question.

[supercat]

What is the preferred way of implementing the semantics: "I want the database to contain exactly one record with a PK of xxx, and some particular columns as specified, setting remaining columns to particular values if no record exists, but leaving them alone if one does"? Do an insert which may fail (duplicate key) and do an update if the insert fails, somehow try an insert and update using one SQL transaction, do a SELECT to see if the record exists, do an insert if not (hoping nobody else beats me to it) and update otherwise, or what? Assume record will exist 99% of the time.

[Andy Lester]

Try to SELECT the record. If it's there, UPDATE the existing one. If not, INSERT a new one.

[Cooper]

Well aren't you sassy!