sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)

iSQlServer發表於2009-07-14
SQL隨機
應用例子:比如飲料的中獎號碼,N多瓶每瓶有個隨機號碼
o覺得有些地方值得學習:

1.一次性插入儘可能多的記錄(不判斷是否已經存在該隨機數),而不是一次插入一條記錄(插入前要判斷是否已經存在該隨機數).這裡重複記錄通過設定忽略重複記錄的索引來實現的.
 "一次性的插入儘可能多的記錄"和"忽略重複記錄的索引"節約的時間簡直太多了....我使用建立索引後的迴圈插入50W記錄都花了2-3個小時....

2.SET NOCOUNT ON 這樣也可以節約時間 ,但測試發現設定為OFF或ON,其時間幾乎相同的都是47或46秒左右

3.這裡雖然也使用了迴圈,但它的迴圈次數是不固定的.其次數取和每次插入失敗的記錄數有關.失敗的記錄數為0的時候,任務也就完成了.我測試需要迴圈50次左右就可以了.

4.syscolumns和sysobjects兩個表的作用是獲得足夠數量的隨機數,兩個表的交叉查詢記錄大於100W就可以了.

sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)--生成8位長度的100W條記錄的隨機數,插入資料庫.
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)USE tempdb
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)GO
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)CREATE TABLE tb(id char(8))
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)CREATE UNIQUE INDEX IX_tb ON tb(id)
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)WITH IGNORE_DUP_KEY  -- --忽略重複記錄的索引
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)GO
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)DECLARE @dt datetime
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)SET @dt = GETDATE()
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)SET NOCOUNT OFF
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)DECLARE @row int
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)SET @row = 1000000 --記錄總數為100W
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)WHILE @row >0
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)BEGIN
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理) RAISERROR('need %d rows', 10, 1, @row) WITH NOWAIT
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理) SET ROWCOUNT @row
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理) INSERT tb SELECT
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理) id = RIGHT(100000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 8)
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理) --FROM syscolumns c1, syscolumns c2  -- SET NOCOUNT ON 49 Second
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理) FROM syscolumns c1, sysobjects o -- SET NOCOUNT ON 47 Second
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理) SET @row = @row - @@ROWCOUNT  --設定下次迴圈需要插入的記錄數量
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)END
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)SELECT BeginDate = @dt, EndDate = GETDATE(), Second = DATEDIFF(Second, @dt, GETDATE())
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)GO
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)SELECT COUNT(*) FROM tb
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)GO
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)DROP TABLE tb
sql 生成100W條指定位數的隨機數的方法(只花費了不足1分鐘)(整理)

--測試結果:
BeginDate                 EndDate                               Second 
2006-10-26 16:03:17.483  2006-10-26 16:04:04.093  47

need 1000000 rows
Duplicate key was ignored.
need 975756 rows
Duplicate key was ignored.
need 951522 rows
Duplicate key was ignored.
need 927292 rows
Duplicate key was ignored.
need 903082 rows
Duplicate key was ignored.
need 878862 rows
Duplicate key was ignored.
need 854648 rows
Duplicate key was ignored.
need 830435 rows
Duplicate key was ignored.
need 806230 rows
Duplicate key was ignored.
need 782030 rows
Duplicate key was ignored.
need 757841 rows
Duplicate key was ignored.
need 733669 rows
Duplicate key was ignored.
need 709495 rows
Duplicate key was ignored.
need 685307 rows
Duplicate key was ignored.
need 661143 rows
Duplicate key was ignored.
need 636988 rows
Duplicate key was ignored.
need 612827 rows
Duplicate key was ignored.
need 588674 rows
Duplicate key was ignored.
need 564527 rows
Duplicate key was ignored.
need 540378 rows
Duplicate key was ignored.
need 516240 rows
Duplicate key was ignored.
need 492132 rows
Duplicate key was ignored.
need 468030 rows
Duplicate key was ignored.
need 443902 rows
Duplicate key was ignored.
need 419780 rows
Duplicate key was ignored.
need 395707 rows
Duplicate key was ignored.
need 371617 rows
Duplicate key was ignored.
need 347528 rows
Duplicate key was ignored.
need 323431 rows
Duplicate key was ignored.
need 299338 rows
Duplicate key was ignored.
need 275260 rows
Duplicate key was ignored.
need 251171 rows
Duplicate key was ignored.
need 227119 rows
Duplicate key was ignored.
need 203050 rows
Duplicate key was ignored.
need 178982 rows
Duplicate key was ignored.
need 154966 rows
Duplicate key was ignored.
need 130920 rows
Duplicate key was ignored.
need 106881 rows
Duplicate key was ignored.
need 82863 rows
Duplicate key was ignored.
need 58852 rows
Duplicate key was ignored.
need 34807 rows
Duplicate key was ignored.
need 10777 rows
Duplicate key was ignored.
need 104 rows
Duplicate key was ignored.
need 1 rows

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-609103/，如需轉載，請註明出處，否則將追究法律責任。

相關文章