CTE，内连接用于千万级数据执行效率的问题

[zyjmib]

此问题源于这个：http://bbs.csdn.net/topics/390414239
当表中的数据为1000W这样的数量级时，两者效率相差无几。
但是，当表里的数据量为6000W时，两个方法的效率出现了明显差异
执行结果：
内连接 用时2:16:28 (12853950 行受影响)
表 'zzz'。扫描计数 2，逻辑读取 1588718 次，物理读取 1102 次，预读 794133 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

CTE 用时18:50 (12853950 行受影响)
表 'zzz'。扫描计数 2，逻辑读取 1588718 次，物理读取 875 次，预读 793743 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
客户端统计信息结果

请教各位大侠，造成两种方法效率相差近十倍的原因

[daiyueqiang2045]

http://www.cnblogs.com/worfdream/articles/2840582.html

[daiyueqiang2045]

http://www.cnblogs.com/worfdream/category/252255.html

[黄_瓜]

去看了半天也没找到你跑的是那两个语句 麻烦贴一下。

[zyjmib]

CTE语句

with cte1 
  as
 (
   select id,listbh,[dealrq],[dealsj],xlbh from zzz     
 ),
  cte2 
  as
 (
   select id,listbh,[dealrq],[dealsj],xlbh from zzz     
 )
 select * from cte1,cte2 
 where cte1.id=cte2.id-1 
 and cte1.listbh=cte2.listbh
 and cte1.[dealrq]=cte2.[dealrq] 
and convert(int,(SUBSTRING(cte2.dealsj,1,4)))-convert(int,(SUBSTRING(cte1.dealsj,1,4)))<=130 
and cte1.xlbh<>cte2.xlbh

内连接语句

select * from zzz c
inner join zzz p 
on c.id = p.id-1
   and c.listbh=p.listbh 
   and c.[dealrq]=p.[dealrq] 
   and convert(int,(SUBSTRING(p.dealsj,1,4)))-convert(int,(SUBSTRING(c.dealsj,1,4)))<=130 
   and p.xlbh<>c.xlbh

[haitao]

不管哪种，都需要索引正确才行 convert(int,(SUBSTRING(p.dealsj,1,4)))-convert(int,(SUBSTRING(c.dealsj,1,4)))<=130 则怎么也无法利用索引了

[黄_瓜]

引用 4 楼 zyjmib 的回复:

CTE语句

with cte1 
  as
 (
   select id,listbh,[dealrq],[dealsj],xlbh from zzz     
 ),
  cte2 
  as
 (
   select id,listbh,[dealrq],[dealsj],xlbh from zzz     
 )
 select * from cte1,cte2 
 where cte1.id=cte2.id-1 
 and cte1.listbh=cte2.listbh
 and cte1.[dealrq]=cte2.[dealrq] 
and convert(int,(SUBSTRING(cte2.dealsj,1,4)))-convert(int,(SUBSTRING(cte1.dealsj,1,4)))<=130 
and cte1.xlbh<>cte2.xlbh

内连接语句

select * from zzz c
inner join zzz p 
on c.id = p.id-1
   and c.listbh=p.listbh 
   and c.[dealrq]=p.[dealrq] 
   and convert(int,(SUBSTRING(p.dealsj,1,4)))-convert(int,(SUBSTRING(c.dealsj,1,4)))<=130 
   and p.xlbh<>c.xlbh

！！！！！！ 这两个完全一样啊。 我说呢逻辑读取次数一模一样，时间上的差异是因为你先执行了第一个有了缓存 第一个语句的物理读取次数就少了很多，所以快了很多 这么大的数据量，存在 and convert(int,(SUBSTRING(p.dealsj,1,4)))-convert(int,(SUBSTRING(c.dealsj,1,4)))<=130 这种列上使用函数计算的情况性能当然很差了。

[zyjmib]

又被自己乌龙了。 内连接语句里多了个”order by id“ 去掉后，也是10多分钟出结果…… 是不是多了排序就能对效率产生这么大的影响？

[zyjmib]

内连接的

[黄_瓜]

引用 7 楼 zyjmib 的回复:

又被自己乌龙了。 内连接语句里多了个”order by id“ 去掉后，也是10多分钟出结果…… 是不是多了排序就能对效率产生这么大的影响？

排序操作开销很大的。

[haitao]

如果结果集的记录数少，排序应该开销不大了