分享：《SQLSERVER2005技术内幕——T-SQL》实例！！！！

Aaron_Chan 2012-05-29 12:33:37

各位，大婶，屌丝们：
由于刚刚对原帖进行了修改，导致看不明白，屌丝现来整理一下：
声明：本文来自博客园左其盛老师的文章:链接：http://www.cnblogs.com/zuoqs/archive/2010/01/24/1655451.html

题目如下：
快速生成一个数据表,只有一列,这一列的值从1到8000。
当然题目中强调的就是速度。



创建表的语句如下：

CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);

-----------------------------------------------------
添加数据：
方法1：引用原帖屌丝（benbenLY）的代码



DECLARE @i INT

set @i=1

WHILE @i<8000

BEGIN

  INSERT INTO Nums SELECT @i

  SET @i=@i+1

END

方法2：此方法是@左其盛老师，运用《SQLserver2005技术内幕—T-SQL》中的算法实现。





DECLARE @max AS INT, @rc AS INT;

SET @max = 8000;

SET @rc = 1;



INSERT INTO Nums VALUES(1);

WHILE @rc * 2 <= @max

BEGIN

  INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;

  SET @rc = @rc * 2;

END



INSERT INTO dbo.Nums 

  SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;

GO

方法3：@周强老师（博客地址：www.cnblogs.com/zhouqiang52154/）对@左老师方法的回应。



;with t1 as

(

select 1 as col

union all

select 1

)

,t2 as

(

select i.col 

from t1 as i 

cross join t1 as p

cross join t1 as q

)

,t3 as

(

select i.col

from t2 as i 

cross join t2 as p 

)

,t4 as

(

select i.col

from t3 as i

cross join t3 as p

cross join t3 as q

)

select top (8000) row_number() over(order by t4.col) 

from t4

@周强老师与@左老师的互动参照：http://www.cnblogs.com/zuoqs/archive/2010/01/24/1655451.html

说说本屌丝对以上三种方法的理解：

第一种，循环很简单，此处略去几个字。
第二种，就是将8000个数据，反批次插入数据表中，每次的插入结果尽可能的依赖与表中原有的数据。
第三种，屌丝虽然心中有点明白，但也不是特别明白，也怕说不出个知乎者也，求大婶们帮忙解答。

同时，大婶们有好的方法，请分享，让大家一起学习。
接下来：是本屌丝对以上三种方法的简单测试：
1.屌丝方法（循环）结果如下：

2.@左老师方法结果如下：

3.@周强老师方法结果如下：

附：通过本屌丝的测试，速度排序是这样的：@周老师，@左老师，@屌丝（benbenLY），然后在@左老师的博客中，@左老师说他的执行结果比@周老师快。这让我很疑惑，因为我的测试结果是，@周老师会比@左老师的速度块很多。再次请大婶们指教。屌丝再次谢过了。

...全文

433 42 打赏收藏转发到动态举报

写回复

用AI写文章

42 条回复

切换为时间正序

请发表友善的回复…

发表回复

lian_liancong 2012-08-21

打赏
举报

我只是来享受这种纯技术的讨论氛围虽然我一点都不懂。你们太强大了！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

孤独加百列 2012-05-30

打赏
举报

其实快就快在没有IO，缓存要比磁盘快得多。第二种方法改成表变量也会快一些。
我的方法其实局限性和第三种一样，数字越大构造的层数越多，效率会变慢但是第三种方法的t5可以满足的数字最大为68719476736这个数字已经可以满足基本上现实的需求了（按美分算盖茨的钱不够貌似。。。），我的到L5最大数字为4294967296比你的第三种少了1个数量级快一点也在情理之中，其实原理都一样，这个就是已平方（三次方）为增长的迭代算法（计算机适合做迭代运算），只是SQL Server实现起来方便而已。你的方法3是三次方，平方，三次方。。。的规律
我的是平方，平方，平方，平方。。。
他的数字增长比我迅速，代价自然比我的高，但是相同层数情况下方法3的可取数区间要比我大。

--小F-- 2012-05-29

打赏
举报

mayuanf 2012-05-29

打赏
举报

公布最快的方法：



select 1

union 

select 2

union

.

.

.

--以下省略8000字

bean_sql 2012-05-29

打赏
举报

Aaron_Chan 2012-05-29

打赏
举报

-------------------------------------------------------
*******************************************************

大婶做总结，大家欢迎啊。
*******************************************************
-------------------------------------------------------

Aaron_Chan 2012-05-29

打赏
举报

[Quote=引用 37 楼的回复:]
引用 36 楼的回复:
其实低三种方法是有局限的，如果要求的数字超过262144的话，需要再嵌套一层循环去构造基础表的。随着生成数字的增大，第三种方法效率会变低的，但是会不会比第一，二种差就要实验看了

我始终认为，你的方法和第三种方法是一致的啊?
[/Quote]

经过屌丝多次测试：magician547 大婶完胜！！！！！！！！

8000条时，-大婶的数据可以进入100（93，95）和@周老师相当。

5w条数据测试结果：
--------------------------------------------------
magician547 大婶 560ms
gogodiy 大婶 4093ms
@周老师 2123ms
@左老师 10796ms
--------------------------------------------------
最后，求magician547 大婶总结，大家欢迎！！！！

Aaron_Chan 2012-05-29

打赏
举报

[Quote=引用 36 楼的回复:]
其实低三种方法是有局限的，如果要求的数字超过262144的话，需要再嵌套一层循环去构造基础表的。随着生成数字的增大，第三种方法效率会变低的，但是会不会比第一，二种差就要实验看了
[/Quote]
我始终认为，你的方法和第三种方法是一致的啊?

孤独加百列 2012-05-29

打赏
举报

其实低三种方法是有局限的，如果要求的数字超过262144的话，需要再嵌套一层循环去构造基础表的。随着生成数字的增大，第三种方法效率会变低的，但是会不会比第一，二种差就要实验看了

Aaron_Chan 2012-05-29

打赏
举报

[Quote=引用 34 楼的回复:]
我这边看执行计划和第三种差很多，我的比他的少做了2次笛卡尔积。
每层L中间表是以平方的方式生成为1的数据，这样L4是256的平方个1，然后rownumber生成整数序列。
L0 1,1
L1 1,1,1,1
L2 1,1,1,1,1,1,1,1，1,1,1,1,1,1,1,1,
。。。。。。
这样不涉及到读表，没有IO开销，都在缓存里进行了，所以会快点。
仔细看了下，LZ的第三种方法……
[/Quote]
嗯，你的方法已经很好了，大婶啊。佩服佩服。

Aaron_Chan 2012-05-29

打赏
举报



gogodiy 大婶方法执行时间：



select top 8000 ID=row_number() over (order by getdate()) 

from master..spt_values a,master..spt_values b

--186ms

-------------------

孤独加百列 2012-05-29

打赏
举报

我这边看执行计划和第三种差很多，我的比他的少做了2次笛卡尔积。
每层L中间表是以平方的方式生成为1的数据，这样L4是256的平方个1，然后rownumber生成整数序列。
L0 1,1
L1 1,1,1,1
L2 1,1,1,1,1,1,1,1，1,1,1,1,1,1,1,1,
。。。。。。
这样不涉及到读表，没有IO开销，都在缓存里进行了，所以会快点。
仔细看了下，LZ的第三种方法生成数字是
t1 1,1
t2 1,1,1,1,1,1,1,1
t3 64个1
t4 64的3次方个1
比我少循环了一次，但是中间两层是3次方的貌似开销比较大

Aaron_Chan 2012-05-29

打赏
举报



单凭时间来说：第3中方法要快于magician547大婶的方法

declare @d datetime

set @d=getdate()

;

with

L0 as (select 1 as ID union all select 1),

L1 as (select a.ID from L0 as a , L0 as b),

L2 as (select a.ID from L1 as a , L1 as b),

L3 as (select a.ID from L2 as a , L2 as b),

L4 as (select a.ID from L3 as a , L3 as b),

L5 as (select row_number()over(order by ID) as ID from L4)



SELECT *  FROM L5 WHERE ID < 8001;

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

--156ms

---------------------------------------------------------

--@周老师方法：93ms

---------------------------------------------------------

--@左老师方法：720ms

Aaron_Chan 2012-05-29

打赏
举报

[Quote=引用 27 楼的回复:]
引用 22 楼的回复:

SQL code

with
L0 as (select 1 as ID union all select 1),
L1 as (select a.ID from L0 as a , L0 as b),
L2 as (select a.ID from L1 as a , L1 as b),
L3 as (select a.ID from L2 as a ……
[/Quote]

我水平有效，目前我通过的测试，很难看出你和第三种直接的差距，反正就是很快就对了。我把你们“执行计划”做了对比，发现，你们的的执行计划基本一致，也就是说，思路应该是一样的。

望大婶解答。感谢你的好方法。

勿勿 2012-05-29

打赏
举报

8000 没必要递归第二种方法快

Aaron_Chan 2012-05-29

打赏
举报

孤独加百列 2012-05-29

打赏
举报

Aaron_Chan 2012-05-29

打赏
举报

[Quote=引用 25 楼的回复:]
数据量放到100000，单纯从执行时间来看，我有个方法和方法3差不多：

SQL code

select top 100000 ID=row_number() over (order by getdate())
from master..spt_values a,master..spt_values b

但是我这个方法问题在于：
SQL Server 分析和编译时间:……
[/Quote]

感谢，大婶的分析，本题重点在于学习到了，数据库的执行问题。

gogodiy 2012-05-29

打赏
举报

数据量放到100000，单纯从执行时间来看，我有个方法和方法3差不多：



select top 100000 ID=row_number() over (order by getdate()) 

from master..spt_values a,master..spt_values b

但是我这个方法问题在于：
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

(100000 行受影响)
表 'spt_values'。扫描计数 2，逻辑读取 11 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 15 毫秒，占用时间 = 1245 毫秒。

而楼主提供的方法3，没有扫描计数，没有逻辑读取，没有IO读取，单就这点而言是最有效率的。

孤独加百列 2012-05-29

打赏
举报

[Quote=引用 22 楼的回复:]

SQL code

with
L0 as (select 1 as ID union all select 1),
L1 as (select a.ID from L0 as a , L0 as b),
L2 as (select a.ID from L1 as a , L1 as b),
L3 as (select a.ID from L2 as a , L2 as b),
L4 as (s……
[/Quote]

每层L中间表是以平方的方式生成为1的数据，这样L4是256的平方个1，然后rownumber生成整数序列。不涉及到IO问题。查询分析器分析执行计划和第三种比较1：99.。。