CODEWITHSUNDEEP
sqlsql-server-2014
Rupert
Rupert

Reputation: 51

Inserting into an empty table is much faster than inserting into a populated table, why?

I'm loading some data from table2 into table1 on SQL Server 2014. The tables have the same fields, though they are not in the same order. I'm selecting each field and inserting them into the destination. 

INSERT INTO table1(column1, column2, etc) SELECT(column1, column2, etc) FROM table2;

If I run this insert command on an empty table that is identical to table2, it moves the 3.2 million rows in a minute and a half. When I run this on table1 (14Million rows of with existing data), it takes almost 3 hours for the same 3.2 million rows to be inserted. So table1 should end up being 17.3 Million rows after the insert. Order doesn't matter.

Why?

Is it because the fields are in a different order? I deleted the indexes from table1, so that's not it. There are no keys or triggers defined.

Here is the estimated plan: https://i.sstatic.net/IuCOv.png

Upvotes: 3

Views: 2095

Answers (2)

Rupert
Rupert

Reputation: 51

I found this note which does not really apply to SQL Server 2014, but I thought I'd give it a try.

https://support.microsoft.com/en-us/kb/297861

I created a clustered index on table1, tried my insert and it finished in 4 minutes. I also tried it with a non-clustered index but I killed it after 30 minutes.

I'm not that well versed on why this is behaving like this, but it is. I'm just glad to have the records inserting in a more acceptable time. Thanks for the pointers everyone.

Upvotes: 2

Ambareesh Surendran
Ambareesh Surendran

Reputation: 508

When you have an empty table

The B-Tree for the indices are also empty. So SQL Server don't have to sort the tree.

When you have a table with 3.2 million records

The B-Tree for the indices are not empty, so SQL Server will have to sort the tree for each record that we insert. So it will take time.

Upvotes: 0

Related Questions