|
|
|
|
公众号矩阵

SQL Server数据库随机抽取数据效率优化总结篇

本文主要介绍了提高SQL Server数据库随机抽取数据时的速度的方法,文中通过四个代码示例来逐渐推荐作者的思路,从而使解决思路更加的清晰易懂,希望能对读者有所帮助。

作者:菜光来源:博客园|2011-07-26 17:19

在操作SQL Server数据库时如何快速的从数据表里随机抽取数据呢?本文通过四个代码示例来比较,逐步地总结经验,来讲述提高随机抽取数据速度的方法,接下来我们就开始介绍。

假设我生成了一个简单的数据表,并且导入了500W条数据进行测试。

下图为***次采用的SQL语句:

  1. Declare @d Datetime  
  2.  
  3. Set @d=getdate()  
  4.  
  5. SELECT top 1[ActivityID]  
  6.  
  7. ,[CardNo]  
  8.  
  9. ,[Password]  
  10.  
  11. ,[State]  
  12.  
  13. ,[CreateTime]  
  14.  
  15. ,[GetTime]  
  16.  
  17. FROM [TGBus_Card].[dbo].[Ka_Card] Where ActivityID = 501 And State = 0 Order By NEWID()  
  18.  
  19. Select [语句执行花费时间(毫秒)]=Datediff(ms,@d,Getdate()) 

这是我优先会想到的解决方案,NEWID()每次都要检索整个数据表,为每一行数据产生一个uniqueidentifier类型的***值,所以它的执行效率肯定不高 ,每次执行都要在4秒左右。

还有没有更好的办法那?!

我在想,因为SELECT是在ORDER BY之前执行的,所以能不能在SELECT的时候产生NEWID()后在ORDER BY使用那?

于是有了第二个版本:

  1. Declare @d Datetime  
  2.  
  3. Set @d=getdate()  
  4.  
  5. SELECT top 1[ActivityID]  
  6.  
  7. ,[CardNo]  
  8.  
  9. ,[Password]  
  10.  
  11. ,[State]  
  12.  
  13. ,[CreateTime]  
  14.  
  15. ,NEWID() as Random  
  16.  
  17. FROM [TGBus_Card].[dbo].[Ka_Card] Where ActivityID = 501 And State = 0 Order By Random  
  18.  
  19. Select [语句执行花费时间(毫秒)]=Datediff(ms,@d,Getdate()) 

确实有所提高!!但太不明显了。。。。。

还有没有更好的办法?!

于是我又想到了2005的新特性TABLESAMPLE,马上试试!

这是第三版的代码:

  1. Declare @d Datetime  
  2.  
  3. Set @d=getdate()  
  4.  
  5. SELECT top 1[ActivityID]  
  6.  
  7. ,[CardNo]  
  8.  
  9. ,[Password]  
  10.  
  11. ,[State]  
  12.  
  13. ,[CreateTime]  
  14.  
  15. ,[GetTime]  
  16.  
  17. FROM [TGBus_Card].[dbo].[Ka_Card] tablesample (1000 Rows) Where ActivityID = 501 And State = 0 
  18.  
  19. Select [语句执行花费时间(毫秒)]=Datediff(ms,@d,Getdate()) 

速度太快了!!!10几毫秒就显示了!

于是小研究了一下TABLESAMPLE,不看不知道,原来TABLESAMPLE随机选取的单位是数据页,也就是说如果数据量很小的话很可能有取不到数据的可能,试一下果然如此!也就是说别的问题又出现了。

问题又回到了起点。。。难道真的就要用NEWID了吗?正当山人我感慨的时候在MSDN上无意中看到了这段文字:

SQL Server数据库随机抽取数据效率优化总结篇

感觉有戏!!

于是又写了这第四版代码:

  1. Declare @d Datetime  
  2.  
  3. Set @d=getdate()  
  4.  
  5. SELECT Top 1* FROM [TGBus_Card].[dbo].[Ka_Card]   
  6.  
  7. WHERE 0.01 >= CAST(CHECKSUM(NEWID(), CardNo) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)  
  8.  
  9. And ActivityID = 501 And State = 0 
  10.  
  11. Select [语句执行花费时间(毫秒)] = Datediff(ms,@d,Getdate()) 

这也是我最终采用的方法,SQL执行速度在1毫秒左右。

相信通过上面的介绍,SQL Server随机抽取数据的快捷方法也就很明显了,就是利用上面的代码片段4中的方法,速度是最快的。本文就介绍到这里,希望本次的介绍能够带给您一些收获。

【编辑推荐】

  1. SQL SERVER 数据挖掘之理解内容类型
  2. SQL SERVER数据挖掘之理解列的用法
  3. SQL Server数据库主键及复合主键的配置
  4. SQL Server数据库如何更改SA密码和默认端口号
  5. SQL Server 2008 Analysis Services建立多维数据集
【责任编辑:赵鹏 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

16招轻松掌握PPT技巧

16招轻松掌握PPT技巧

GET职场加薪技能
共16章 | 晒书包

289人订阅学习

20个局域网建设改造案例

20个局域网建设改造案例

网络搭建技巧
共20章 | 捷哥CCIE

645人订阅学习

WOT2019全球人工智能技术峰会

WOT2019全球人工智能技术峰会

通用技术、应用领域、企业赋能三大章节,13大技术专场,60+国内外一线人工智能精英大咖站台,分享人工智能的平台工具、算法模型、语音视觉等技术主题,助力人工智能落地。
共50章 | WOT峰会

0人订阅学习

读 书 +更多

网络管理员备考训练——计算机与网络基础知识

本书是根据全国计算机技术与软件专业技术资格(水平)考试《网络管理员考试大纲》所要求的考试范围而编写的试题集。全书共分10个单元,同步...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO播客