表变量特点补充，为什么表变量在大数据情况下普遍性能不佳

之前有写一篇文章

http://blog.csdn.net/starseeker7/article/details/8621046

今天看到论坛上在讨论这个东西，顺便更详细的补充一下

--2014/06/07补充：

我们再优化SQL时，经常会发现若对表变量填充大量数据，性能往往会不如使用临时表做相同动作

那么到底是上面的哪一点造成这个情况的呢？

最近看到一篇文章比较详细的测试了一下，发现是上面第六点最具有决定性的影响。

参考地址：http://blogs.msdn.com/b/psssql/archive/2010/08/24/query-performance-and-table-variables.aspx

测试环境搭建

set statistics profile off
go
create table t2 (c2 int)

go
--insert 100,000 rows into the perm table
set nocount on

declare @i int
set @i = 0
while @i < 100000
begin
insert into t2 values (@i)
set @i = @i + 1
end
go
--update stats
update statistics t2

--测试底性能的执行计划

--2.  join permantant table with table variable the table variable gets 100,000 rows inserted then it is joined to t2 @t1 gets 1 rows estimate it ends up with nested loop join

set nocount on
declare @t1 table (c1 int)
begin tran
declare @i int
set @i = 0
while @i < 100000
begin
insert into @t1 values (@i)
set @i = @i + 1
end
commit tran
set statistics profile on

set statistics IO on
select * from @t1 inner join t2 on c1=c2
go

set statistics profile off
set statistics IO off
go

--使用option关键字，进行强制重编译优化后的执行计划

--3. solution use stmt level recompile

declare @t1 table (c1 int)
set nocount on
begin tran
declare @i int
set @i = 0
while @i < 100000
begin
insert into @t1 values (@i)
set @i = @i + 1
end
commit tran
set statistics profile on

set statistics IO on
select * from @t1 inner join t2 on c1=c2 option (recompile)
go

set statistics profile off
set statistics IO off
go

我们可以看到而这性能差异很大，具体导致原因我们通过通过statistics profile获取的执行计划对比得出

他们的esimaterows区别很大，同样是table scan@t1第一次仅识别到1行，而第二个查询中，才能正确世界到10W行数据

这直接导致了他们采取了不同的inner join方式，第一个使用的是nested join而第二个使用的是hash match

而大数据情况下hash match具有非常明显的优势。这也就是为什么使用表值函数在应用到较多数据的情况下性能低下的根本原因。