How Can I Efficiently Update a Table with 120 Million Records?

Update Strategies for 120 Million Records: An Efficient Approach

Updating a table containing 120 million records can be a daunting task. The default update approach can exhaust resources and take an extended period. To overcome this challenge, a more efficient method involves using a SELECT statement to populate a separate table.

Steps for Populating a Secondary Table

For tables without a clustered index and during a period without concurrent DML, the following steps can be applied:

1. Clone the BaseTable: Use a SELECT statement to create a clone.BaseTable that includes the data from dbo.BaseTable and a new INT column set to -1.
2. Rectify the Clone: Recreate indexes, constraints, etc., on the clone table.
3. Swap Tables: Utilize ALTER SCHEMA ... TRANSFER to swap the original and cloned tables.
4. Cleanup: Drop the old table.

For tables with clustered indexes or other complexities, a more detailed process is required:

Detailed Process for Non-Simple Scenarios

1. Recreate BaseTable: Clone the original table into a clone schema with a clustered index, identity columns, computed columns, and the new INT column.
2. Exclude Constraints: Omit triggers, foreign key constraints, non-clustered indexes, and check constraints.
3. Test Insert: Perform a small insert operation to validate the cloned table's functionality.
4. Truncate Clone: Clear the clone table.
5. Bulk Insert: Perform a bulk insert into the clone table in bulk-logged or simple recovery mode.
6. Recreate Constraints: Recreate all non-clustered constraints, indexes, triggers, and check constraints one by one.
7. Schema Transfer: Move the cloned table to the dbo schema and the original table to a backup schema.
8. True-Up Operation: If applicable, perform any necessary true-up operations.

Conclusion

By utilizing this select-and-populate approach, it becomes feasible to update large tables efficiently. This method can significantly reduce the time required for table updates, making it a viable solution for tables with a substantial number of records.

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