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



...全文
501 42 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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
本书是Inside Microsoft SQL Server 2005系列四本著作中的一本。它详细介绍了T-SQL的内部体系结构，包含了非常全面的编程参考，提供了使用Transact-SQL（T-SQL）的专家级指导，囊括了非常全面的编程参考，揭示了基于集合的查询的强大威力，并包含大量来自专家们的参考和建议。本书适合专业数据库开发者、BI开发者、DBA和以SQL Server作为后台数据库的一般应用程序开发者，读者可以通过书中的最佳实践、高级技巧和代码示例来掌握这门复杂的编程语言，以切合实际的方案来解决复杂的实际问题。 　　深入理解T-SQL体系结构，充分利用高级T-SQL查询技术。 　　本书深入介绍了T-SQL的内部体系结构，揭示了基于集合的查询的强大威力，并包含大量来自专家们的参考和建议。通过本书提供的最佳实践和示例代码，数据库开发人员和管理员完全可以掌握这门复杂的编程语言，以切合实际的方案来解决复杂的实际问题。通过本书，你将学习到如何：理解逻辑和物理的查询处理；使用方法论优化查询；在查询中用TOP选项修改数据；用递归逻辑、具体化路径或嵌套集合解决方案查询特殊的数据结构；通过逻辑难题提高你的逻辑能力并掌握查询问题的核心等。 　　你将学习到如何： 　　理解逻辑和物理的查询处理； 　　使用方法论优化查询； 　　解决关系分区问题； 　　使用CTE和排名函数简化及优化解决方案； 　　用各种技术聚合数据，包括附加属性、旋转、直方图和分组因子； 　　在查询中用TOP选项修改数据； 　　用递归逻辑、具体化路径或嵌套集合解决方案查询特殊的数据结构； 　　通过逻辑难题提高你的逻辑能力并掌握查询问题的核心； 内容简介 本书是Inside Microsoft SQL Server 2005系列四本著作中的一本。本书及其续篇——《Microsoft SQL Server 2005技术内幕：T-SQL程序设计》介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。这两本书侧重于解决实践中的常见问题，并讨论了解决这些问题的方法。它们将向你揭示基于集合（set-based）查询的强大威力，并解释为什么它比使用游标的过程化编程（procedural programming）更具优势。同时，它还会教你识别使用基于游标解决方案与基于集合解决方案的优劣。 　　书中还讲述了其他几种争议较多的构造（camstruct）——如临时表、动态执行、XML和.NET集成——它们在具有强大功能的同时，也具有极大的风险。 　　本书适合于需要编写或检查T-SQL代码的有经验的T-SQL程序员和数据库专业人员。读者可从中学到大量精湛的技巧，这些技巧会充实您的工具箱和编码技能，并让您顺利地开发出高效的解决方案。 作者简介 Itzik Ben-Gan是Solid Quality Learning的首席导师和创始人。他从1999年开始便一直是SQL Server方面的Microsoft MVP，在世界各地讲授　T-SQL查询、编程和查询优化相关的课程，并提供相关咨询服务。他在SQL Server Magazine和MSDN上发表了多篇文章，并被邀请在许多专题会议上做过报告，包括TechEd、DevWeek、PASS和SQL Server Connections。 目录 序 前言 致谢 引言 　本书的组织 　系统要求 　安装示例数据库 　更新 　代码示例 　本书支持 第1章　逻辑查询处理 　逻辑查询处理中的各个阶段 　　逻辑查询处理阶段简介 　Customers/Orders场景下的示例查询 　逻辑查询处理步骤详解 　　步骤1：执行笛卡尔乘积（交叉联接） 　　步聚2：应用ON筛选器（联接条件） 　　步骤3：添加外部行（Outer　Row） 　　步骤4：应用WHERE筛选器 　　步骤5：分组 　　步骤6：应用CUBE或ROLLUP选项 　　步骤7：应用HAVING筛选器 　　步骤8：处理SELECT列表 　　步骤9：应用DISTINCT子句 　　步骤10：应用ORDER　BY子句 　　步骤11：应用TOP选项 　SQL Server 2005中新的逻辑处理阶段 　　表运算符 　　OVER子句 　　集合操作 　结论 第2章　物理查询处理 　查询处理期间的数据流 　编译 　　Algebrizer 　　优化 　　使用查询计划 　　更新计划 　结论 　　致谢 第3章　查询优化 　本章用到的示例数据 　优化方法论 　　分析实例级的等待 　　联系等待和队列 　　确定方案 　　细化到数据库/文件级别 　　细化到进程级别 　　优化索引/查询 　查询优化工具 　　syscacheobjects 　　清空缓存 　　动态管理对象 　　STATISTICS　IO 　　测量查询的运
SQL Server 2005技术内幕 存储引擎
SQL Server 2005技术内幕 存储引擎
Server+2005技术内幕-存储引擎
Server+2005技术内幕-存储引擎 资源共享！！
深入体验Java Web开发内幕——核心基础
深入体验Java Web开发内幕——核心基础 深入体验Java Web开发内幕——核心基础
深入体验Java_Web开发内幕—核心基础 完整版 pdf
深入体验Java_Web开发内幕—核心基础 pdf,完整版本,字迹清楚,需要的下吧
应用实例

27,582

社区成员

68,544

社区内容

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

加载中

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

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

+ 用AI写文章