SQL Server數(shù)據(jù)庫隨機抽取數(shù)據(jù)效率優(yōu)化總結(jié)篇
在操作SQL Server數(shù)據(jù)庫時如何快速的從數(shù)據(jù)表里隨機抽取數(shù)據(jù)呢？本文通過四個代碼示例來比較，逐步地總結(jié)經(jīng)驗，來講述提高隨機抽取數(shù)據(jù)速度的方法，接下來我們就開始介紹。
假設(shè)我生成了一個簡單的數(shù)據(jù)表，并且導(dǎo)入了500W條數(shù)據(jù)進行測試。
下圖為***次采用的SQL語句：
- Declare @d Datetime
- Set @d=getdate()
- SELECT top 1[ActivityID]
- ,[CardNo]
- ,[Password]
- ,[State]
- ,[CreateTime]
- ,[GetTime]
- FROM [TGBus_Card].[dbo].[Ka_Card] Where ActivityID = 501 And State = 0 Order By NEWID()
- Select [語句執(zhí)行花費時間(毫秒)]=Datediff(ms,@d,Getdate())
這是我優(yōu)先會想到的解決方案,NEWID()每次都要檢索整個數(shù)據(jù)表，為每一行數(shù)據(jù)產(chǎn)生一個uniqueidentifier類型的***值，所以它的執(zhí)行效率肯定不高 ，每次執(zhí)行都要在4秒左右。
還有沒有更好的辦法那？！
我在想，因為SELECT是在ORDER BY之前執(zhí)行的，所以能不能在SELECT的時候產(chǎn)生NEWID()后在ORDER BY使用那？
于是有了第二個版本：
- Declare @d Datetime
- Set @d=getdate()
- SELECT top 1[ActivityID]
- ,[CardNo]
- ,[Password]
- ,[State]
- ,[CreateTime]
- ,NEWID() as Random
- FROM [TGBus_Card].[dbo].[Ka_Card] Where ActivityID = 501 And State = 0 Order By Random
- Select [語句執(zhí)行花費時間(毫秒)]=Datediff(ms,@d,Getdate())
確實有所提高！！但太不明顯了。。。。。
還有沒有更好的辦法？！
于是我又想到了2005的新特性TABLESAMPLE，馬上試試！
這是第三版的代碼：
- Declare @d Datetime
- Set @d=getdate()
- SELECT top 1[ActivityID]
- ,[CardNo]
- ,[Password]
- ,[State]
- ,[CreateTime]
- ,[GetTime]
- FROM [TGBus_Card].[dbo].[Ka_Card] tablesample (1000 Rows) Where ActivityID = 501 And State = 0
- Select [語句執(zhí)行花費時間(毫秒)]=Datediff(ms,@d,Getdate())
速度太快了！！！10幾毫秒就顯示了！
于是小研究了一下TABLESAMPLE,不看不知道，原來TABLESAMPLE隨機選取的單位是數(shù)據(jù)頁，也就是說如果數(shù)據(jù)量很小的話很可能有取不到數(shù)據(jù)的可能，試一下果然如此！也就是說別的問題又出現(xiàn)了。
問題又回到了起點。。。難道真的就要用NEWID了嗎？正當山人我感慨的時候在MSDN上無意中看到了這段文字：
感覺有戲！！
于是又寫了這第四版代碼：
- Declare @d Datetime
- Set @d=getdate()
- SELECT Top 1* FROM [TGBus_Card].[dbo].[Ka_Card]
- WHERE 0.01 >= CAST(CHECKSUM(NEWID(), CardNo) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)
- And ActivityID = 501 And State = 0
- Select [語句執(zhí)行花費時間(毫秒)] = Datediff(ms,@d,Getdate())
這也是我最終采用的方法，SQL執(zhí)行速度在1毫秒左右。
相信通過上面的介紹，SQL Server隨機抽取數(shù)據(jù)的快捷方法也就很明顯了，就是利用上面的代碼片段4中的方法，速度是最快的。本文就介紹到這里，希望本次的介紹能夠帶給您一些收獲。
【編輯推薦】
