分享：《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），然后在@左老师的博客中，@左老师说他的执行结果比@周老师快。这让我很疑惑，因为我的测试结果是，@周老师会比@左老师的速度块很多。再次请大婶们指教。屌丝再次谢过了。



...全文
431 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
  • 打赏
  • 举报
 回复
[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
  • 打赏
  • 举报
 回复
[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]
被忽视了，LZ测测我的效率呗。。。
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.。。
加载更多回复（19）
Microsoft_SQL_Server_2005技术内幕：T-SQL查询.pdf
本书及其续篇——《Microsoft SQL Server 2005技术内幕：T-SQL程序设计》介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。这两本书侧重于解决实践中的常见问题，并讨论了解决这些问题的方法。它们...
C程序设计语言（第2版·新版中文）
sqlserver2005技术内幕:t-sql程序设计part1(中文完整版分4份下) C# 编程实例与技巧 本周上传用户排名 122个star20100906 43个aa3323036 28个sai5201314vicky 28个ltx0724 26个fengboss2010 23个zhoulei3862422 20...
实战化课程：全面掌握SQL Server高可用技术，从菜鸟到专家
本课程根据讲师十多年在世界500强外企的生产环境中的SQL Serer数据库管理和项目实施经验倾心打造。课程系统性强，知识体系完整，覆盖90%以上的企业环境下SQL Server高可用场景，课程中不仅演示详细的操作步骤，更加突出最常见的故障和问题，让学员少走“弯路”，不只是让学员学会“操作”更能让学员“操作”的规范，满满的干货分享，一些课程资料（架构图、部署规划表格等）不仅可以帮助学员掌握技能，也可以作为学员在企业生产环境中实施SQL Server高可用的配置文档、操作手册等。课程的实验环境介绍：1）全部基于微软域环境和企业版SQL Server AOAG - 95%以上的企业环境都是在域环境中，不介绍非域环境和标准版的SQL Server高可用性组，这的配置在企业中较罕见，没有实践意义，不浪费学员时间。2）相应域环境已提前部署和配置好 - 学员导入虚拟机即可开始实验，无需从零开始搭建域环境，所有实验中SQL Server均已加域，直入主题，节省大量时间。3）最新的Windows Server故障转移集群（WS2016、WS2019）和最新版本的SQL Server（SQL2017、SQL2019） -  WS2016-SQL2017与WS2019-SQL2019是目前大多数企业SQL Server高可用的主要平台，基于微软产品生命周期现在一些企业也在讲早期的AOAG向这两个版本迁移，掌握这两种组合不仅让学员学会，更能学有所用。本课程为后续SQL Server进阶课程铺垫，是通向SQL Server DBA 专家的必经之路，讲师每周答疑两次。所有课程资料包括：课程PPT、架构图、部署规划表格、各类脚本学员均可下载。     
ADO.NET技术内幕 part4
本书还介绍了ADO.NET对象模型，用于Web扩展性的XML特性，与SQL Server 2000的集成。 全书共分5大部分。第I部分提供了ADO.NET对象模型的概要叙述，并且提供了【数据窗体向导】的指导内容。第Ⅱ部分介绍了如何使用.NET...
若干源程序资料12.rar
2012-06-11 21:06 3,769,694 基于LABVIEW编程环境的SQLSERVER ACCESS MYSQL 数据库的测试源代码.rar 2012-06-11 21:12 81,505 基于神经网络的文字识别系统.rar 2012-06-11 21:07 39,498 基因遗传算法.zip 2012-06-11...
应用实例

27,579

社区成员

68,557

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章