Insert into temp table with clustered index performance

559 views Asked by At

I've tested two scenarios A and B. I was surprised that scenario A took 11 seconds and B took 16 seconds.

A.

  • 1) Create temp table
  • 2) Create a clustered index on that temp table
  • 3) Use INSERT INTO SELECT to Insert 2 million records into this temp table

B.

  • 1) Create temp table with SELECT INTO (same data as in A)
  • 2) Create clustered index on that table

Could you explain why A was faster than B in this case? When looking at execution plans, A is inserting into clustered index. B is inserting into a heap and then into clustered index upon index creation. Could it be the reason for overhead? I was somehow convinced that droping all indexes before insert and recreating them after would be faster in all cases.

0

There are 0 answers