Unexpected #temp table performance

Question

Bounty open: Ok people, the boss needs an answer and I need a pay rise. It doesn't seem to be a cold caching issue.

UPDATE:

I've followed the advice below to no avail. How ever the client statistics threw up an interesting set of number.

#temp vs @temp

Number of INSERT, DELETE and UPDATE statements 0 vs 1

Rows affected by INSERT, DELETE, or UPDATE statements 0 vs 7647

Number of SELECT statements 0 vs 0

Rows returned by SELECT statements 0 vs 0

Number of transactions 0 vs 1

The most interesting being the number of rows affected and the number of transactions. To remind you, the queries below return identical results set, just into different styles of tables.

---

The following query are basicaly doing the same thing. They both select a set of results (about 7000) and populate this into either a temp or var table. In my mind the var table @temp should be created and populated quicker than the temp table #temp however the var table in the first example takes 1min 15sec to execute where as the temp table in the second example takes 16 seconds.

Can anyone offer an explanation?

declare @temp table ( 
id uniqueidentifier, 
brand nvarchar(255), 
field nvarchar(255),
date datetime, 
lang nvarchar(5), 
dtype varchar(50)
)
insert into @temp (id, brand, field, date, lang, dtype )
select id, brand, field, date, lang, dtype
from view 
where brand = 'myBrand' 
-- takes 1:15

vs

select id, brand, field, date, lang, dtype
into #temp
from view 
where brand = 'myBrand'

DROP TABLE #temp
-- takes 16 seconds

Answer 1

I believe this almost completely comes down to table variable vs. temp table performance.

Table variables are optimized for having exactly one row. When the query optimizer chooses an execution plan, it does it on the (often false) assumption that that the table variable only has a single row.

I can't find a good source for this, but it is at least mentioned here:

http://technet.microsoft.com/en-us/magazine/2007.11.sqlquery.aspx

Other related sources:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125052

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Answer 2

Run both with SET STATISTICS IO ON and SET STATISTICS TIME ON. Run 6-7 times each, discard the best and worst results for both cases, then compare the two average times.

I suspect the difference is primarily from a cold cache (first execution) vs. a warm cache (second execution). The output from STATISTICS IO would give away such a case, as a big difference in the physical reads between the runs.

And make sure you have 'lab' conditions for the test: no other tasks running (no lock contention), databases (including tempdb) and logs are pre-grown to required size so you don't hit any log growth or database growth event.