CODEWITHSUNDEEP
sqlpostgresql
Nursultan
Nursultan

Reputation: 69

When it is better to truncate and insert all data then update

I have a scheduler to a service to get help data(about 15 000) for every 30 mitues.

What practice is better ?

  1. To truncate db and insert all every time

or

  1. To create a 3 collection (insert, update and delete) and in post-processing to update these help data by necessity

Upvotes: 1

Views: 4951

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

In general, inserts are faster than updates and deletes because new records can just be added "at the end of the table", which minimizes locking. Of course, this can be altered by the presence of indexes and triggers.

That suggests that the solution just doing inserts sounds better.

However, the two are not the same. If you truncate and insert, then all the data is only new data. If you insert/update/delete, then you will have a mixture of old and new data.

So, if you want only clean data, use insert. If you want "progressive" data, then modify as you go.

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

It might depend on the indexes, number of update or delete needed. Number of columns of the table could be in play along with normally how many columns to be updated.

But since there are 15000 rows. I would go for truncate and insert.

Upvotes: 2

Related Questions