Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

UPDATE vs INSERT performance

Tags:

Am I correct to assume that an UPDATE query takes more resources than an INSERT query?

like image 283
user838437 Avatar asked Sep 04 '11 14:09

user838437

People also ask

Which is faster insert or update?

UPDATE is much faster than DELETE+INSERT sql server.

Is update better than delete and insert?

For best future query performance, it's better to do an update to keep the same extents. Delete and insert will not necessarily use the same extents. For a table of that size, it would be unlikely to do so.

What is the difference between insert and update?

Insert is for adding data to the table, update is for updating data that is already in the table.

Which is faster delete or insert?

Inserting rows in a table is faster than deleting them. Loading data into a new table using create-table-as-select (CTAS) is faster still.

2 Answers

I am not a database guru but here my two cents:

Personally I don't think you have much to do in this regard, even if INSERT would be faster (all to be proven), can you convert an update in an insert?! Frankly I don't think you can do it all the times.

During an INSERT you don't usually have to use WHERE to identify which row to update but depending on your indices on that table the operation can have some cost.

During an update if you do not change any column included in any indices you could have quick execution, if the where clause is easy and fast enough.

Nothing is written on stones and really I would imagine it depends on whole database setup, indices and so on.

Anyway, found this one as a reference:

Top 84 MySQL Performance Tips

like image 108
Davide Piras Avatar answered Dec 16 '22 08:12

Davide Piras

If you plan to perform a large processing (such as rating or billing for a cellular company), this question has a huge impact on system performance.

Performing large scale updates vs making many new tables and index has proven to reduce my company billing process form 26 hours to 1 hour!

I have tried it on 2 million records for 100,000 customer.
I first created the billing table and then every customer summary calls, I updated the billing table with the duration, price, discount.. a total of 10 fields.

In the second option I created 4 phases.
Each phase reads the previous table(s), creates index (after the table insert completed) and using: "insert into from select .." I have created the next table for the next phase.

Summary
Although the second alternative requires much more disk space (all views and temporary tables deleted at the end) there are 3 main advantages to this option:

  1. It was 4 time faster than option 1.
  2. In case there was a problem in the middle of the process I could start the process from the point it failed, as all the tables for the beginning of the phase were ready and the process could restart from this point. If the process fails implementing the first option, you will need to start the all the process all over again.
  3. This made the development and QA work much faster as they could work parallel.
like image 42
GK10 Avatar answered Dec 16 '22 07:12

GK10
Sign in to Comment

Related questions

C++ iterator to const_iterator
What is the system color of disabled text?
SSL works with browser, wget, and curl, but fails with git
jQuery DataTables Filtering for Specific Columns Only
What is the difference between IIS reset and application pool recyle in affecting of cache
MySql How to set a local variable in an update statement (Syntax?)
What are Indexes in the Xcode Core-Data data model inspector
Improving CI build time (.NET)
spring java configuration unit test
How to use std::atomic efficiently
Clang on Windows
Include .cpp instead of header(.h)

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!