4

Why is insert a) so slow compared to insert b)?

When executing the select statement select_statement below (without the insert into part), the query terminates in app. 4 seconds. It is a select from a indexed table with appr. 30 million records.

The insert statement a) terminates in 11 seconds(??)

The insert statement b) terminates in 4 seconds (the same amount of time as the select itself)

a)

declare @Test table
(
  stackerNbr varchar(9)
)
insert into @Test(stackerNbr) select select_statement

b)

create table #Test(stackerNbr varchar(9))
insert into #Test(stackerNbr) select select_statement
Stuart Ainsworth
  • 12,792
  • 41
  • 46
Jakob Mathiasen
  • 1,267
  • 3
  • 15
  • 18
  • Check this: http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server – Pred Apr 11 '14 at 11:13
  • Have a look at [Using with vs declare a temporary table: performance / difference?](http://stackoverflow.com/questions/5435319/using-with-vs-declare-a-temporary-table-performance-difference) – huMpty duMpty Apr 11 '14 at 11:13
  • Well, in the http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server the difference is described, but they don't mention why the inserts perform so differently. The select_statement returns 195 rows from a table with 30 million records. But to insert 195 records into a table (declared table or created temp table) should not be the issue.. It seems that the insert into @Test(stackerNbr) select select_statement does not use index. The insert b) does – Jakob Mathiasen Apr 11 '14 at 11:30

0 Answers0