7 mil rows: Update vs Insert

Question

OS: Windows Server 2003 R2 64 bit Enterprise SP2

SQL Server: 2008 R2 64-bit Enterprise

There are 7 millions records involved in an update. I was wondering if insert into a new table with updated values (and dropping the old table) is faster than updating the existing table. There are no indexes on this table. Disk space is not an issue. This table is not partitioned.

I look forward to your guidance. I understand that all such performance questions are answered with "It Depends". But, just checking if there are other answers or facts that I need to consider.

Thank you.

-Jeelani

Answer 1

I guess if you already have the correct data in another table then dropping the old table and inserting the latest data in a new table with the same name would be easier and faster.

---

Please mark this as the answer or vote as helpful, as appropriate, to make it useful for other readers. Thanks!
Aalam (http://aalamrangi.wordpress.com/)

Answer 2

There are 7 millions records involved in an update. I was wondering if insert into a new table with updated values (and dropping the old table) is faster than updating the existing table. There are no indexes on this table. Disk space is not an issue. This table is not partitioned.

as long as you don't change the clustered index (probably your primary key) I would expect no performance improvement when you insert the updated data into new table and dropping the old table because you read all records and write all records so there's no difference in number of writes of the effective data. In addition to the real data additional records for the table metadata, clustered index etc. have to been written when using an additional table but not when updating the existing records.

The situation may become different if you're looking at the overhead for transaction log and that depends on the Recovery Model of the database (and eventually some additional flag). Inserting the data into a new table probably generates less transactions in the log file but that depends on how you're inserting the data.

But why don't you just perform it with a copy of this table as you've enough disk space.

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

Hello

updating the existing table could involve less I/O. Note that the transaction log file could grow quickly. check its size when you are done

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 4

My advice is - try it!

My expectation is that doing a bulk load into a new table will be much, much faster.

Josh

ps - without an index the update is liable to be slow anyway - though a merge update can be fast, but hey is it even a pure update anyway or IS it a merge operation?  in which case the new table tends to win once again.