How Can I Efficiently Update 120 Million Records in a Database Table?

Optimizing Mass Record Update Operations

Problem:
Updating a massive table of 120 million records using a straightforward UPDATE statement has failed, even after running for several hours.

Solution:

Instead of updating the existing table, utilize a SELECT statement to populate a new table with the desired values. This approach significantly improves performance and avoids the bottlenecks associated with direct updates.

Steps for Non-Simple Cases:

1. Create a New Table with the Desired Structure:

   • Recreate the original table under a different schema with the same clustered index, identity columns, computed columns, and the new column to be updated.
   • Exclude triggers, foreign key constraints, and other non-essential elements.

2. Test the Insert Operation:

   • Insert a small subset of rows into the new table to ensure correctness.

3. Prepare for Bulk Insert:

   • Set the database to bulk-logged or simple recovery model.
   • Truncate the new table.

4. Perform the Bulk Insert:

   • Insert all records from the original table into the new table using a single INSERT statement with the TABLOCK hint.

5. Verify Data Integrity:

   • Check the new table to confirm the successful update.

6. Recreate Constraints and Triggers:

   • Recreate all non-clustered constraints, indexes, and triggers in the new table while maintaining the schema in a separate batch.

7. Switch Tables:

   • Move the original table to a backup schema and the new table to the desired schema.
   • Perform any necessary true-up operations to reconcile any changes that occurred during the update process.

The above is the detailed content of How Can I Efficiently Update 120 Million Records in a Database Table?. For more information, please follow other related articles on the PHP Chinese website!