Recently a friend asked me, he said that it was very slow for him to delete millions to tens of millions of data in SQLSERVER. I helped him analyze it and made some suggestions as follows. It may be useful to many people, and it is also very long. I haven’t written a blog yet, let’s discuss it together.
If your hard disk space is small, and you don’t want to set the database log to the minimum (because you want other normal logs to still be recorded), and you have relatively high speed requirements, and clear all data, I suggest you Use turncate table1, because truncate is a DDL operation, it does not generate rollback, and it is faster if it does not write logs. Then if there is an auto-increment, it will be restored to 1, and delete will generate rollback. If you delete a table with a large amount of data, the speed will be very slow. , it will occupy a lot of rollback segments, and also record G-level logs; of course, what if there are conditional deletions such as where time<'2006-3-10'? Can I use delete without logging? The answer is no. , the SQL Server engine is designed to log Delete operations. So far there is no way to force certain statements not to be recorded in the log. If Delete Table1 where Time < '2006-3-10' is executed, the log record will be very large (3-4G) because there are many records involved. If possible, I suggest the following method:
select the records you need to keep to a new table. If you use Full Recovery Mode
Depending on the number of SELECT INTO records, the log may be larger.
Select * into Table2 From Table1 Where Time > = '2006-03-10'
and then directly Truncate Table1. No logging will occur regardless of the recovery model
Truncate table Table1
and finally rename Table2 to Table1
EC sp_rename 'Table2', 'Table1'
Source: BLOG:domino's column