SQL语言艺术 第11章 精于计谋：挽救响应时间 枢轴表

 

一.创建枢轴表

1.单个CTE

测试返回一千万行记录表的时间为2:19，最简单的写法，测试结果显示效率最低

declare @n bigint
set @n=10000000;

with number (row_num) as
(select 1 as row_num
  union all
   select row_num + 1
      from number
      where row_num + 1 < @n)
   select * from number
OPTION  (maxrecursion 0)

  

2.多个CTE联结

测试返回一千万行记录表的时间为1:19，结果最大数值依赖于联结的次数，如过联结M次，则最大数量为2的2的M-1次方，例如只有cte1，则数量为2的2的（1-1）次方，即2的1次方，2条记录；如果有cte2，则数量为2 的2的（2-1）次方，即2的2次方，4条记录；以此类推……

declare @n bigint
set @n=10000000;

with cte1 as
(select 1 as c union all select 1),
cte2 as
(select 1 as c from cte1 a,cte1 b),
cte3 as
(select 1 as c from cte2 a,cte2 b),
cte4 as
(select 1 as c from cte3 a,cte3 b),
cte5 as
(select 1 as c from cte4 a,cte4 b),
cte6 as
(select 1 as c from cte5 a,cte5 b),
nums as
(select row_number() over(order by c) as n from cte6)
select n from nums where n<=@n

 

3.利用已存在的系统表联结

测试返回一千万行记录表的时间为1:36，结果最大数值依赖于已存在表的行数，如syscolumns记录有N行，联结M次，则最大数量为N的M次方

 declare @n bigint
set @n=10000000;

SELECT TOP @n id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b ,syscolumns c
select * from #
drop table # 

 

二.枢纽表作用

1.使记录倍增

with number (row_num) as
(select 1 as row_num
  union all
   select row_num + 1
      from number
      where row_num + 1 < 10) 
select movies.*
from movies, number
where number.row_num <= 3 
OPTION  (maxrecursion 0)

 

结果：

movie_id    actors
----------- --------------------------------------------------
1           123,456,78,96
2           23,67,97
3           67,456
1           123,456,78,96
2           23,67,97
3           67,456
1           123,456,78,96
2           23,67,97
3           67,456

(9 row(s) affected)