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



...全文
500 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)
本书是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   测量查询的运

27,582

社区成员

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

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