0

I have to update multiple records in a table with the most efficient way out there having the least latency and without utilising CPU extensively. At a time records to update can be ranged from 1 to 1000.

We do not want to lock the database when this update occurs as other services are utilising it.

Note: There are no dependencies generated from this table towards any other table in the system.

After looking in many places I've drilled down a few ways to do the task-

  1. simple-update: A simple update query to the table with update command with already known id's
    1. Either multiple update queries (one query for each individual record), or
    2. Usage of update ... from clause as mentioned here as a single query (one query for all records)
  2. delete-then-insert: Firstly, delete the outdated data and then insert updated data with new id's (since there is no dependency on records, new id's are acceptable)
  3. insert-then-delete: Firstly, insert updated records with new id's and then delete outdated data using old id's (since there is no dependency on records, new id's are acceptable)
  4. temp-table: Firstly, insert updated records into a temporary table. Secondly, update the original table with inserted records from the temporary table. At last, remove the temporary table.
    1. We must not drop the existing table and create a new one in its place
    2. We must not truncate the existing table because we have a huge number of records that we cannot store in the buffer memory

I'm open to any more suggestions.

Also, what will be the impact of making the update all at once vs doing it in batches of 100, 200 or 500?

References:

kartoon
  • 910
  • 1
  • 8
  • 20
  • Why do you think UPDATE is not efficient? And how do you measure the efficiency? – Frank Heikens May 30 '22 at 07:54
  • I never mentioned that `update` is not efficient, I simply want to know how does it behave in comparison with other ways of updating the records. I'm measuring efficiency in terms of CPU utilisation and time taken to update. – kartoon May 30 '22 at 07:57
  • You are the only one who can test the updates on your data on your server, while you also have your other server load. You can play with the FILLFACTOR (and other server settings), be selective with the usage of indexes (that also need to be updated), etc. UPDATE ... FROM ... would be my starting point, to keep things simple and standardised. – Frank Heikens May 30 '22 at 08:53
  • Only (1). (2) and (3) do too much work, and (4) is even beyond that. – wildplasser May 30 '22 at 11:13

0 Answers0