cte和临时表性能分析

CTE和临时表性能比较
CTE最优秀的地方是在实现递归操作，和替代绝大部分游标的功能，但是对于大数据量，由于cte不能建索引，所以明显比临时表差。

例如：当需要查询大数据时，临时表的性能远比CTE要高，以下两个查询结果相同，但是CTE 性能明显不如临时表
CTE查询，耗时60S
if OBJECT_ID('tempdb.dbo.#POUnsign')is not null
drop table#POUnsign;
;with cte_POUnsignWF as
(
select distinct a.Applicationid collate SQL_Latin1_General_CP1_CI_AS as Applicationid --必须使用AS 重命名字段，否则cte查询的结果中没有列名
,a.Applicant collate SQL_Latin1_General_CP1_CI_AS as Applicant
,f.Approver collate SQL_Latin1_General_CP1_CI_AS as Approver
,erManager collate SQL_Latin1_General_CP1_CI_AS as UserManager
,f.FlowDescription collate SQL_Latin1_General_CP1_CI_AS as FlowDescription
from[OAWF2].[QSMCWF].[dbo].[WFApprove]a with(nolock)
inner join[OAWF2].[QSMCWF].[dbo].[WFFlow]f with(nolock)
on a.ApplicationID=f.ApplicationID and a.FormID=f.FormID and
a.CurrentFlowNo=f.FlowNo
inner join dbo.tscUser u with(nolock)
on a.Applicant=erName collate SQL_Latin1_General_CP1_CI_AS
where a.FormID='WF0032'and f.ApproveStatus='4'and a.ApplyStatus='0'and
f.SequenceNo=1
)
select distinct pu.Applicant,pu.Approver,erManager,pu.FlowDescription
into#POUnsign
from cte_POUnsignWF pu
inner join dbo.tdsPoSignInfo ps with(nolock)on pu.Applicationid=ps.ApplicationID collate SQL_Latin1_General_CP1_CI_AS
inner join dbo.tdsPoHeader ph with(nolock)on ph.Ebeln=ps.Ebeln collate
SQL_Latin1_General_CP1_CI_AS
left join dbo.tdsAupo a with(nolock)on a.EBELN=ps.Ebeln collate
SQL_Latin1_General_CP1_CI_AS and a.EBELP='00010'collate SQL_Latin1_General_CP1_CI_AS where ph.Sexkz in('0','1')and a.DECLITEM is null
select*from#POUnsign
drop table#POUnsign
临时表查询：耗时8s
if OBJECT_ID('tempdb.dbo.#POUnsign')is not null
drop table#POUnsign;
if OBJECT_ID('tempdb.dbo.#POUnsignWF')is not null
drop table#POUnsignWF;
select distinct a.Applicationid collate SQL_Latin1_General_CP1_CI_AS as Applicationid
--必须使用AS 重命名字段，否则查询的结果中没有列名，不能创建临时表
,a.Applicant collate SQL_Latin1_General_CP1_CI_AS as Applicant
,f.Approver collate SQL_Latin1_General_CP1_CI_AS as Approver
,erManager collate SQL_Latin1_General_CP1_CI_AS as UserManager
,f.FlowDescription collate SQL_Latin1_General_CP1_CI_AS as
FlowDescription
into#POUnsignWF
from[OAWF2].[QSMCWF].[dbo].[WFApprove]a with(nolock)
inner join[OAWF2].[QSMCWF].[dbo].[WFFlow]f with(nolock)
on a.ApplicationID=f.ApplicationID and a.FormID=f.FormID and a.CurrentFlowNo=f.FlowNo inner join dbo.tscUser u with(nolock)
on a.Applicant=erName collate SQL_Latin1_General_CP1_CI_AS
where a.FormID='WF0032'and f.ApproveStatus='4'and a.ApplyStatus='0'and f.SequenceNo=1
select distinct pu.Applicant,pu.Approver,erManager,pu.FlowDescription
into#POUnsign
from#POUnsignWF pu
inner join dbo.tdsPoSignInfo ps with(nolock)on pu.Applicationid=ps.ApplicationID collate SQL_Latin1_General_CP1_CI_AS
inner join dbo.tdsPoHeader ph with(nolock)on ph.Ebeln=ps.Ebeln collate
SQL_Latin1_General_CP1_CI_AS
left join dbo.tdsAupo a with(nolock)on a.EBELN=ps.Ebeln collate
SQL_Latin1_General_CP1_CI_AS and a.EBELP='00010'collate SQL_Latin1_General_CP1_CI_AS where ph.Sexkz in('0','1')and a.DECLITEM is null
select*from#POUnsign
drop table#POUnsign
drop table#POUnsignWF
以下是从网上引用的一篇文章，说的很言简意赅，也解释了我的疑问。

临时表、表变量、CTE的比较
1、临时表
临时表包括：以#开头的局部临时表，以##开头的全局临时表。

a、存储
不管是局部临时表，还是全局临时表，都会放存放在tempdb数据库中。

b、作用域
局部临时表：对当前连接有效，只在创建它的存储过度、批处理、动态语句中有效，类似于C语言中局部变量的作用域。

全局临时表：在所有连接对它都结束引用时，会被删除，对创建者来说，断开连接就是结束引用；对非创建者，不再引用就是结束引用。

但最好在用完后，就通过drop table 语句删除，及时释放资源。

c、特性
与普通的表一样，能定义约束，能创建索引，最关键的是有数据分布的统计信息，这样有利于优化器做出正确的执行计划，但同时它的开销和普通的表一样，一般适合数据量较大的情况。

有一个非常方便的select ... into 的用法，这也是一个特点。

2、表变量
a、存储
表变量存放在tempdb数据库中。

b、作用域
和普通的变量一样，在定义表变量的存储过程、批处理、动态语句、函数结束时，会自动清除。

c、特性
可以有主键，但不能直接创建索引，也没有任何数据的统计信息。

SQL Server是以表变量的数据在上千条前提，来生成执行计划的，所以表变量适合数据量相对较小的情况。

必须要注意的是，表变量不受事务的约束，下面的例子说明了这一点：
declare@tb table(v int primary key,vv varchar(10))
begin tran
insert into@tb
select 1,'aa'
rollback tran
--虽然上面回滚了事务,但还是会返回1条记录
select*from@tb
begin tran
update@tb
set vv='bb'
where v= 1
rollback tran
--返回的数据显示,update操作成功,根本没有回滚
select*from@tb
3、CTE
CTE，就是通用表表达式。

a、存储
产生的数据一般存储在内存，不会持久化存储。

也可以持久化：
;with cte
as
(
select 1 as v,'aa'as vv
union all
select 2,'bb'
)
--把cte的数据存储在tb_cte表
select*into tb_cte
from cte
select*from tb_cte;
--运用cte，删除数据
;with cte_delete
as
(
select*from tb_cte
)
delete from cte_delete where V= 1
--返回1条数据，另一条已删除
select*from tb_cte
当然，在实际运行时，有些部分，比如假脱机，会把数据存储在tempdb的worktable、workfile中，另外，一些大的hash join和排序操作，也会把中间数据存储在tempdba。

b、作用域
只存在于当前的语句。

c、特性
在同一个语句中，一次定义，可以多次引用。

另外，可以定义递归语句，不过这个递归语句的性能，还不如写个while循环来的好。

之前在上家公司开发报表时，大量使用了CTE，一个CTE中包含了10到20个的语句，最后关联出结果集。

也就是对每个小语句，根据引用语句中相关表的统计信息，估计产生多少行结果，然后再估计这些小语句产生的多个结果集，当再次进行关联时，估计会有多少行结果集，也就是对估计的结果，再次进行估计。

这样偏差就会越来越大，最终往往会导致产生的执行计划不够准确，这样往往会有性能问题。

其实，本质问题就是，一个语句几千行，语句太复杂了，SQL Server很难做出最优化的执行计划，这确实难为SQL Server了，所以后来就把这个CTE改为，每一小段语句，把结果集通过select into插入到临时表中，因为临时表是有统计信息的，这样最后关联多个临时表。

对SQL Server而言，现在有了每个小的结果集的精确的统计信息，那么就自然能做出更为精确的执行计划，执行性能自然上升。