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

Tips for Swiftly Updating 120 Million Records

Encountering performance issues while attempting to update a table with 120 million records is a common challenge. The traditional approach of using an UPDATE statement can be prohibitively slow, requiring hours or even days to complete.

Instead of attempting a direct update, consider populating a second table using a SELECT statement. This approach offers significant performance advantages, as illustrated below:

Simple Case:

• For tables without a clustered index and during periods of low DML activity:

  • Execute: SELECT *, new_col = 1 INTO clone.BaseTable FROM dbo.BaseTable
  • Recreate indexes, constraints, etc. on the new table
  • Switch old and new tables using ALTER SCHEMA ... TRANSFER
  • Drop the old table

Non-Simple Case:

• Recreate the BaseTable with the same name under a different schema (clone.BaseTable)
• Include the clustered index, identity columns, computed columns, and the new INT column
• Exclude triggers, foreign key constraints, non-clustered indexes, and check constraints
• Test the insert with a small number of rows
• Truncate the clone table and perform the full insert
• Recreate all non-clustered constraints, indexes, and triggers
• Move dbo.BaseTable to a backup schema and clone.BaseTable to the dbo schema

By avoiding direct updates and leveraging the power of SELECT statements, you can drastically reduce the update time, making it feasible to update even massive tables efficiently.

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