8

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

Andy Lester
  • 81,480
  • 12
  • 93
  • 144
Saurabh Kumar
  • 2,249
  • 6
  • 31
  • 48
  • If you only need to update a few rows, I don't think performance matters as much as if you need to update all 1 million rows. – BoltClock Oct 09 '10 at 03:47
  • I have updated question to mention that I am targeting SQL 2005/2008 ideally. – Saurabh Kumar Oct 09 '10 at 03:56
  • Think on what you're asking - is doing two operations faster than one? To delete a rec it needs to remove it from the datablock and any associated index blocks. An insert has to do the reverse. An update is optimised to in-place update just as little is as needed, only touching indexes if indexed columns change. So in short, update is much faster. – Will Oct 09 '10 at 04:31
  • What if you have referential integrity in play?? You won't be able to just delete away rows and re-insert them!! – marc_s Oct 09 '10 at 07:15

2 Answers2

16

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.

Andy Lester
  • 81,480
  • 12
  • 93
  • 144
  • 1
    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. – Saurabh Kumar Oct 09 '10 at 15:48
  • 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. – supercat Oct 13 '10 at 19:48
  • Try to SELECT the record. If it's there, UPDATE the existing one. If not, INSERT a new one. – Andy Lester Oct 13 '10 at 19:57
  • Well aren't you sassy! – Cooper May 10 '19 at 01:46
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.

Pradeep
  • 3,174
  • 1
  • 19
  • 34