0

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.

Zobia Kanwal
  • 4,085
  • 4
  • 15
  • 38
neela
  • 77
  • 6
  • How did you test that? Within the same session, B directly after A? Then caching could falsify the result, because a lot of data could already be in the RAM. Concerning speed: Try B with a nonclustered index - that could be the fastest at all. At least if you don't need so many included columns (depends on the queries you optimize against). – Grimm Mar 15 '19 at 08:43
  • Could be to do with minimal logging - you haven't given enough details to know whether this could be at play. Could also be to do with memory grants to sort the rows into clustered index order – Martin Smith Mar 15 '19 at 09:57

0 Answers0