背景
最近SSIS的开发过程中遇到几个问题。其中使用CTE时，遇到一个远程连接对象，结果导致严重的性能问题，为了应急我就修改了代码。
之前我写了一篇介绍CTE的随笔包含了CTE的用法等：
http://wudataoge.blog.163.com/blog/static/80073886200961652022389/
问题
在一个数据查询中遇到一个远程连接对象，然后使用了CTE，然后本地查询与远程对象的CTE进行了left join 。下面就是执行计划：
首先我们发现，最后一个操作符显示远程查询占了99%。
注意：
首先，远程查询使用的是CTE的表达式，我对CTE的理解有以下几点：
1.一次性视图（ADHoc View）。即必须后面跟着相应的select、insert、update等，只能用一次。
2.CTE表达式也是在内存中创建了一个表并对其操作。
3.with as 部分仅仅是一个封装定义的对象，并没有真的查询。
3.除非本身具有索引否则CTE中是没有索引和约束的。
4.没有专门的统计信息，这点与表变量很像。有可能会有错误的统计信息。
其次，连接操作符使用的是循环嵌套的操作符。这样就几何翻倍了查询的时间。
这里需要说一下NestedLoops：
本质上讲，“Nested Loops”操作符就是：为每一个记录的外部输入找到内部输入的匹配行。
技术上讲，这意味着外表聚集索引被扫描获取外部输入相关的记录，然后内表聚集索引查找每一个匹配外表索引的记录。
以上两个说法都表明了这种方式导致的性能问题。因为每一次循环都要访问一次链接服务器。当数据很大的时候极大地增加了查询时间。我这边70000+的数据执行了半小时。
解决：
既然了解了问题的情况，那我就着手解决问题。主要是两分解成两个步骤：
1.将远程链接服务器的查询结果插入临时表。
2.本地数据与临时表做left join。
对应的执行计划如下：
可以看到整个性能得到了极大的提高。修改完成后执行时间缩减到20秒以内。效率还是惊人的。
可以对比一下表变量与cte表倒是不同的特点：
- tempdb中实际存在的表
- 能索引
- 有约束
- 在当前连接中存在，退出后自动删除。
- 有由引擎生成的数据统计。
通过两个方式的不同点可知几种情况不应当使用CTE：
1.结果集较大时不应使用。
2.查询时间较长的不要使用，比如跨服务器查询。
3.需要大的表连接的，比如行很多的各种join。尤其没有索引。
4.多次查询数据。
5.需要优化相关子查询。
这些时候使用临时表甚至表变量将会带来性能的提升。具体我就不在这里细说了有兴趣可以一起讨论下。
一些网上的错误：
1.materialize 提示 可以强制将WITH AS短语里的数据放入一个全局临时表里。sql server中根本没有这个提示。据说２０１４以后可能会有？
２.ＣＴＥ 性能要差，根据实际情况出发，据我所知在绝大多数情况下，ＣＴＥ的性能要好。尤其是对比游标（迭代）和内置函数的情况下，都会大大提高性能。
３.ＣＴＥ使用了ｔｅｍｐｄｂ，没有仅仅使用了内存。
总结：
通过解决实际问题，让我了解了CTE的运行机制。可以理解为一种一次性的视图。当然我们这里需要着重说明，CTE本身在性能优化上还是有很大作用的，尤其对于递归查询和内置函数的使用时都极大的较少了ＩＯ。
我猜想ＣＴＥ内部原理应该与游标相似，但是极大的简化了性能，也许是优化器的功劳。最后由于仅仅使用了内存中这样也大大减少了连接瓶颈。
这部分很多是我的个人观点，希望各位大神帮忙指摘一下。
发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/119721.html原文链接：https://javaforall.cn
【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛
【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...
