SQL 2008 CTE 使用小结。原来CTE 没有Index。顺带问个问题。
标题有点夸张了。不过做了那么久，确实也是刚知道，所以上来通报一声。

本人因为平时写代码比较规范，一般SQL语句都会尽可能简化，而且如果使用CTE,基本上不太会返回大量数据。昨天同事写了存储过程，执行时间竟然超过了10分钟，存储过程本省很简单，前面就是一些变量定义，然后就是使用了CTE语句，CTE返回结果在和5，6个表inner join连接, 表的数据量也就是1，2百万，不算很多。
按照以前的经验，这种语句应该是瞬秒的，但是这次需要这么长时间，肯定不正常。

给几个新表新添了几个Index,但是问题完全没有改善。然后根据自己精良简化语句的原则，改了一下Code，结合刚添加的index,存储过程一下变成瞬秒，执行时间为0秒。后来在网上查了一下，原来CTE没有index的。所以如果CTE返回百万级数据量，然后再和其他表格连接的话，那么超过10分钟的执行时间也是意料之中了。

旧代码 大致如下：
with tb as --返回百万级数据
(
  select ... 
)
select ...
from 表格1
inner join 表格2   --百万级数据
inner join 表格3   --百万级数据
inner join 表格4   --百万级数据
inner join tb
...                --链接几个小数据表格
group by ...
order by ...
执行时间超过10分钟

新代码 大致如下：
with tb as --返回几时行数据
(
  select ... 
  inner join 表格2   --百万级数据
  inner join 表格3   --百万级数据
  inner join 表格4   --百万级数据
)
select ...
from tb
inner join tb
...                --链接几个小数据表格
group by ...
order by ...
执行时间0秒。

再提个问题
我将存储过程的旧代码直接在窗口中执行的话，执行时间大概45秒左右，但是如果作为存储过程，执行时间就超过10分钟，这个上面也浪费我不少时间。
有哪位高人知道是什么原因么？
------解决方案--------------------
应该是连接的顺序问题，把筛选结果最少的关联放到前面去
------解决方案--------------------
你看看2个语句的执行计划，应该是不一样的
------解决方案--------------------
这个应该是2个语句，生成的执行计划不同导致的。

第一个就是旧的代码，你说的，就是没有用到索引，这个应该是慢的原因。

而第二个新的代码，应该是用到了索引。

2种不同的写法，导致了，产生了不同的执行计划。你需要分析一下执行计划，看看两个执行计划，在哪儿不同。
------解决方案--------------------
哦，另外，你建了索引，那么你的语句中的where条件是写在with中的，还是写在：

select ...
from tb
inner join tb
...                --链接几个小数据表格
group by ...
order by ...

中的呢，如果是写在外面，可能是由于，where条件没有带入到with中，导致无法使用索引的。

这个with有点像试图，你写了一个，多处都可以调用。

可能是这个试图没有很好的展开，所以导致where中的条件，没有带入到with中，来过滤数据，所以导致没有使用上索引
------解决方案--------------------

引用:

Quote: 引用:

这个应该是2个语句，生成的执行计划不同导致的。

如果是写在外面，可能是由于，where条件没有带入到with中，导致无法使用索引的

问题1，执行计划肯定是不同的。这个没有疑问的。
问题2，where条件的位置都测试过，对于执行时间没有影响。

现在的情况是，如果with返回大量数据（百万级），再和其他表连接就很慢，因为with返回的结果集是不能添加index的。所以要让with竟可能的返回有用数据，我就将外面的语句挪进去了。新代码中with就返回即使行数据，再和小表格连接就完全没有问题了。这个才是症结。

因为with返回的结果集是不能添加index的

这个是什么意思
------解决方案--------------------
我之前写过比较复杂的报表，就是一个语句中，with有7-8个，

每个小查询中，都有多个表的关联，相应的表建了索引，where子句也直接加在里面，原表数据量比较大有1.5亿，经过时间的查询条件，大概能返回几百万条，通过索引，整个with基本上查询只需要10秒左右
------解决方案--------------------
先顶后看……
------解决方案--------------------

引用:

Quote: 引用:

因为with返回的结果集是不能添加index的

这个是什么意思

如果这样的代码：
with tb a