Speed ​​up an UPDATE row or DELETE it and INSERT a new one?

Question

Given the two scenarios of SQL Server 2008/2005 - 1 table has 5 rows 2 table has 1 million rows

If we need to update multiple lines, what is effective and why? 1) UPDATE required columns 2) DELETE row and INSERT new row with updated information

Answer 1

You should not ask this question. You ask: "Is it better to do it right or wrong, in the name of some vague idea" faster "?

Do you have an application that is too slow? Do you for some reason think that the problem is that your UPDATE is too long? Have you made any measurements and comparative analysis of the effectiveness of interaction with databases?

What you do is a premature worst-case type optimization, and you make your application a poor service. You make wild guesses about how to speed up your code and use absolutely nothing.

Write your code correctly. Then try to find where you have performance issues. Do you even have a performance problem, or are you asking this question simply because you think about it, what should you ask? You should not.

Even if you have a problem with your UPDATE being too slow, we cannot answer the question “Is X faster than Y” because you did not provide us with enough information, for example:

• What database are you using
• Table layouts
• What indexes are in the database
• How do you interact with the database?

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

Answer 2

Typically, updating one line will be faster. The reason is to delete the existing row and insert a new row, both of these operations will affect the clustered index. Updating a single row will also affect different indexes, but not a clustered index. There is no data to support my application, but logically, database engines should behave this way.