多级CTE快速获得数字表的效率分析

/*
读SQL05技术内幕的时候看到一段迅速取得连续数的代码，如1-1亿
对源代码有改动
*/
with
L0 as
(select 1 as c
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1
union all select 1),
L1 as (select 1 as c from L0 as a,L0 as b),
L2 as (select 1 as c from L1 as a,L1 as b),
L3 as (select 1 as c from L2 as a,L2 as b),
nums as (select row_number() over(order by c) as n from L3)
select count(*) from nums
/* 当然也可用下面的查询语句，但请务必记得选上丢弃结果选项，否则太耗时间
select n from nums
End 截至此处代码完毕*/

在自己的机器上大概2s中就能完成上述过程，之后就想这里用了三级CTE才做到，要是在一个CTE中对上一级CTE做多级交叉连接，就可以减少级数，那么随之带来的效率呢？

经过对查询计划的分析和验证，得出以下结论：
在上述代码中，L0中有10行，每个CTE做一次交叉连接，共有三级CTE，那么最后的max(n)=10*exp(2*exp3),在查询中进行了15次交叉连接；
更一般的：
对于L0中有M行数据，每个CTE中有N个表，共k级CTE，那么最后的max(n)=M*exp(N*expk),查询中共进行k*exp(N+1)-1次交叉连接。

所以，要更加快速的更大的n，可以把M取大一些（这不是一个好提议，会有大量的重复代码），N取小一些，K视需要来定。
比如同样要得到最大为10*exp64的数字表，有至少如下两种方案：
1 N取4，k取3，交叉连接255次
2 N取2，k取6，交叉连接127次
--也可以令M=100，N=2，k=3，不过代码会大量重复
显然后者更加优秀

原创文章若转载请注明：转载自imac的博文@http://imac-macheng.blogspot.com/