title: INSERT INTO SELECT and SELECT INTO take much longer than the SELECT
body: <p>I've got a SELECT statement which takes around 500-600ms to execute. If I use the same SELECT in a <code>INSERT INTO ...</code> <code>SELECT ... or SELECT ... INTO</code> it takes up to 30 seconds. </p>
  
  <p>The table is more like a data copy of a view, for performance reasons which gets truncated and filled with the data from time to time. So my SQL looks like:</p>
  
  <pre><code>TRUNCATE myTable
  INSERT INTO myTable (col, col, col) SELECT col, col, col FROM otherTable INNER JOIN ...
  </code></pre>
  
  <p>I tried multiple things like inserting the data into a temp table so no indexes etc. are on the table (well I also tried dropping the indexes from the original table) but nothing seems to help. If I'm inserting the data into the temp table first (which also takes 30 seconds) and then copy it to the real table, the copy itself is pretty fast (&lt; 1 second).</p>
  
  <p>The query results in ~3800 rows and like 30-40 columns.</p>
  
  <p>The second time executing the Truncate-INSERT INTO/SELECT INTO sql takes less than a second (until I clear all caches). The execution plans look the same, except for the Table Insert which has a cost of 90%. </p>
  
  <p>Also tried to get rid of any implicit conversions but that didnt help either.</p>
  
  <p>Someone knows how this can be possible or how I could find the problem? The problem exists on multiple systems running Sql Server 2014/2016.</p>
  
  <p><strong>Edit</strong>: Just saw the execution plan of my SELECT shows an "Excessiv Grant" message as it estimated ~11000 rows but the result is only ~3800 rows. Could that be a reason for the slow insert?</p>