應用例子:比如飲料的中獎號碼,N多瓶每瓶有個隨機號碼o覺得有些地方值得學習:
1.一次性插入儘可能多的記錄(不判斷是否已經存在該隨機數),而不是一次插入一條記錄(插入前要判斷是否已經存在該隨機數).這裡重複記錄通過設定忽略重複記錄的索引來實現的.
"一次性的插入儘可能多的記錄"和"忽略重複記錄的索引"節約的時間簡直太多了....我使用建立索引後的迴圈插入50W記錄都花了2-3個小時....
2.SET NOCOUNT ON 這樣也可以節約時間 ,但測試發現設定為OFF或ON,其時間幾乎相同的都是47或46秒左右
3.這裡雖然也使用了迴圈,但它的迴圈次數是不固定的.其次數取和每次插入失敗的記錄數有關.失敗的記錄數為0的時候,任務也就完成了.我測試需要迴圈50次左右就可以了.
4.syscolumns和sysobjects兩個表的作用是獲得足夠數量的隨機數,兩個表的交叉查詢記錄大於100W就可以了.
--產生8位長度的100W條記錄的隨機數,插入資料庫.
USE tempdb
GO
CREATE TABLE tb(id char(8))
CREATE UNIQUE INDEX IX_tb ON tb(id)
WITH IGNORE_DUP_KEY -- --忽略重複記錄的索引
GO
DECLARE @dt datetime
SET @dt = GETDATE()
SET NOCOUNT OFF
DECLARE @row int
SET @row = 1000000 --記錄總數為100W
WHILE @row >0
BEGIN
RAISERROR('need %d rows', 10, 1, @row) WITH NOWAIT
SET ROWCOUNT @row
INSERT tb SELECT
id = RIGHT(100000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 8)
--FROM syscolumns c1, syscolumns c2 -- SET NOCOUNT ON 49 Second
FROM syscolumns c1, sysobjects o -- SET NOCOUNT ON 47 Second
SET @row = @row - @@ROWCOUNT --設定下次迴圈需要插入的記錄數量
END
SELECT BeginDate = @dt, EndDate = GETDATE(), Second = DATEDIFF(Second, @dt, GETDATE())
GO
SELECT COUNT(*) FROM tb
GO
DROP TABLE tb
--測試結果:
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
