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

之前有写一篇文章

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 offgocreate table t2 (c2 int)go--insert 100,000 rows into the perm tableset nocount ondeclare @i intset @i = 0while @i < 100000begininsert into t2 values (@i)set @i = @i + 1endgo--update statsupdate 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 ondeclare @t1 table (c1 int)begin trandeclare @i intset @i = 0while @i < 100000begininsert into @t1 values (@i)set @i = @i + 1endcommit transet statistics profile onset statistics IO onselect * from @t1 inner join t2 on c1=c2goset statistics profile offset statistics IO offgo

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

--3. solution use stmt level recompiledeclare @t1 table (c1 int)set nocount onbegin trandeclare @i intset @i = 0while @i < 100000begininsert into @t1 values (@i)set @i = @i + 1endcommit transet statistics profile onset statistics IO onselect * from @t1 inner join t2 on c1=c2 option (recompile)goset statistics profile offset statistics IO offgo

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

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

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

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