背景
最近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中根本没有这个提示。据说2014以后可能会有?
2.CTE 性能要差,根据实际情况出发,据我所知在绝大多数情况下,CTE的性能要好。尤其是对比游标(迭代)和内置函数的情况下,都会大大提高性能。
3.CTE使用了tempdb,没有仅仅使用了内存。
总结:
通过解决实际问题,让我了解了CTE的运行机制。可以理解为一种一次性的视图。当然我们这里需要着重说明,CTE本身在性能优化上还是有很大作用的,尤其对于递归查询和内置函数的使用时都极大的较少了IO。
我猜想CTE内部原理应该与游标相似,但是极大的简化了性能,也许是优化器的功劳。最后由于仅仅使用了内存中这样也大大减少了连接瓶颈。
这部分很多是我的个人观点,希望各位大神帮忙指摘一下。
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/119721.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...