SQL优化技巧--远程连接对象引起的CTE性能问题

时间:2021-08-17 18:19:33

背景 

  最近SSIS的开发过程中遇到几个问题。其中使用CTE时,遇到一个远程连接对象,结果导致严重的性能问题,为了应急我就修改了代码。

  之前我写了一篇介绍CTE的随笔包含了CTE的用法等:

http://wudataoge.blog.163.com/blog/static/80073886200961652022389/

问题

  在一个数据查询中遇到一个远程连接对象,然后使用了CTE,然后本地查询与远程对象的CTE进行了left join 。下面就是执行计划:

SQL优化技巧--远程连接对象引起的CTE性能问题

首先我们发现,最后一个操作符显示远程查询占了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。

对应的执行计划如下:

SQL优化技巧--远程连接对象引起的CTE性能问题

可以看到整个性能得到了极大的提高。修改完成后执行时间缩减到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内部原理应该与游标相似,但是极大的简化了性能,也许是优化器的功劳。最后由于仅仅使用了内存中这样也大大减少了连接瓶颈。

  这部分很多是我的个人观点,希望各位大神帮忙指摘一下。