Delete VS Insert vs Insert from one table to another table

Question

Scenario: I have one table with 100 Million records. I want to delete 90 Million of those records. If I do Delete then on certain VCores it takes multiple hours to delete. Increasing the Vcores does not help much as the CPU and LOGIO remain quite low.

Question: Why the increase in the Vcore does not help much?

Scenario: I have one table with 100 Million records. I want to delete 90 Million of those records. Better way to handle this is to just move 10 Million rows that I want to keep in another table and then just rename the table, obviously taking care of any referential constraints, then drop the original table with 100 Million records. This runs like a charm, I get what I want in just 5 minutes.

Question: Why is the insert operation between two tables so fast?

Please share if you have any links that I can refer to and gain more knowledge about the resources used in Delete VS Insert vs Insert from one table to another table.

Thanks in advance

Answer 1

Answer 1: The increase in the Vcores may not help much because the performance of the delete operation may be limited by other factors such as disk I/O or locking and blocking in the database. When you perform a delete operation on a large table, the database must locate the records to be deleted, lock them, and then physically remove the data from the disk. If the disk I/O speed is low or if there is blocking or locking caused by other queries or transactions, the performance of the delete operation may be slow even if you increase the number of Vcores.

Answer 2: The insert operation between two tables may be fast because the database is able to write the data to disk more efficiently when inserting into a new table compared to updating or deleting from an existing table. When you insert into a new table, the database does not have to lock or update any existing data, and it can write the data to disk in a more sequential manner, which can be faster than the random I/O required for updating or deleting from an existing table. Additionally, the database may use indexing and other optimization techniques to make the insert operation faster, which can also contribute to its performance.

Answer 2

@Bas Pruijn I appreciate your contribution. So, I am creating a table and inserting data from another table. Does this not involve any other overhead, such as logging?