Unexpected #temp table performance

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 uniqueident开发者_如何转开发ifier, 
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

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

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.

This is not uncommon. Table variables can be (and in a lot of cases ARE) slower than temp tables. Here are some of the reasons for this:

• SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. Without statistics, SQL Server might choose a poor processing plan for a query that contains a table variable

• Non-clustered indexes cannot be created on table variables, other than the system indexes that are created for a PRIMARY or UNIQUE constraint. That can influence the query performance when compared to a temporary table with non-clustered indexes.

• table variables use internal metadata in a way that prevents the engine from using a table variable within a parallel query (this means that it wont take advantage of multi-processor machines).

• A table variable is optimized for one row, by SQL Server (it assumes 1 row will be returned).

I'm not 100% that this is the cause, but the table var will not have any statistics whereas the temp table will.

SELECT INTO is a non-logged operation, which would likely explain most of the performance difference. INSERT creates a log entry for every operation.

Additionally, SELECT INTO is creating the table as part of the operation, so SQL Server knows automatically that there are no constraints on it, which may factor in.

If it takes over a full minute to insert 7000 records into a temp table (persistent or variable), then the perf issue is almost certainly in the SELECT statement that's populating it.

Have you run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS before profiling? I'm thinking that maybe it's using some cached results for the second query.