1

If I perform the CRUD operations on the same table, what is the heaviest operation in terms of performance?

People say DELETE and then INSERT is better than UPDATE in some cases, is this true? Then UPDATE is the heaviest operation?

wootscootinboogie
  • 7,688
  • 33
  • 99
  • 182
user3332579
  • 2,671
  • 3
  • 14
  • 19
  • 3
    What difference does it make. Are you not going to use the command that accomplishes whatever you are attempting? – Dan Bracuk Feb 27 '14 at 13:16
  • @DanBracuk The difference here is between changing the data and removing the data and then putting it in again. His question is what would be faster? – Elias Feb 27 '14 at 13:18
  • 1
    possible duplicate of [In SQL, is UPDATE always faster than DELETE+INSERT?](http://stackoverflow.com/questions/1271641/in-sql-is-update-always-faster-than-deleteinsert) – Rajesh Dhiman Feb 27 '14 at 13:19
  • No, I've some big amount of background operation that is performed by a WinService. It is run time is more than 10 hours. I just want to optimize it a little bit. When talking about billions, even milliseconds makes difference. – user3332579 Feb 27 '14 at 13:19
  • indexing improves SELECT performance while has negative effect on insert / delete operations – Mudassir Hasan Feb 27 '14 at 13:20
  • You would have to take into account the foreign keys that link to this table you are acting upon. Would there have to be deletes/inserts into other tables? Are the updated fields part of an index? – Elias Feb 27 '14 at 13:20
  • and that post is not duplicate of mine. I'm asking what is the slowest and fastest operation among CRUD operation. I've some chance in my app to reduce the number of `SELECT`s, `DELETE`s, and `UPDATES`s somehow. To decide, I just want to know what is the fastest and what is the slowest. – user3332579 Feb 27 '14 at 13:23
  • can't you test it out yourself? do it each way you are thinking about and see what works faster. anything else is pure speculation. we have no idea of your schema, data or hardware. – KM. Feb 27 '14 at 13:46
  • Select queries are not part of CRUD? Why are they being mentioned? – Dan Bracuk Feb 27 '14 at 13:50

2 Answers2

4

Like all things in life, it depends.

SQL Server uses WAL (write ahead logging) to maintain ACID (Atomicity, Consistency, Isolation, Durability) properties.

A insert needs to log entries for data page and index page changes. If page splits occur, it takes longer. Then the data is written to the data file.

A delete marks the data and index pages for re-use. The data will still be there right after the operation.

A update is implemented as an delete and insert. There for double the log entries.

What can help inserts is pre-allocating the space in the data file before running the job. Auto growing the data files is expensive.

In summary, I would expect updates on average to be the most expensive operation.

I am by no way an expert on the storage engine.

Please check out http://www.sqlskills.com - Paul Randals blog and/or Kalen Daleny SQL Server Internals book, http://sqlserverinternals.com/. These authors go in depth on all the cases that might happen.

CRAFTY DBA
  • 12,337
  • 3
  • 21
  • 27
  • Thanks. I was thinking about decreasing the number of updates, I seem I'm on the right way. – user3332579 Feb 27 '14 at 13:37
  • Great breakdown. Also of note, `INSERT`, `UPDATE`, and `DELETE` also lock the referenced table, which can impact other queries trying to access it. – valverij Feb 27 '14 at 14:11
  • Thanks for the answer. How costly are `group by` and `order by` compared to the `insert`, `update` and `delete` mentioned above (in a general sense)? – jack Mar 02 '21 at 14:40
0

It depends mostly on foregin keys and indexes which you have on this table. For deletion and isertion every column that is a foreign key and part of an index has to be checked on foreign key references and every index containing that column has to be rebuilt.

If you do DELETE and then INSERT then checking and rebuilding happens twice. If it is a really large table then rebuilding indexes can take very long time and in this case update will be MUCH faster.

Of course if you have index on the key that you're searching with update statement and you are not updating the key.

For a small table with almost no indexes/foreign keys the operations run so fast that it's not a big issue.

Ziouas
  • 519
  • 1
  • 4
  • 18