大量新增連續資料

by adonis 29. 七月 2013 16:33

作    者:楊先民
審    稿:張智凱

前言
一直想寫這篇文章,因為實在太多人曾經問過,如何新增資料時自行產生一個「自動編號」的欄位,重點是這哥們想要自己寫,不想要依賴 SQL Server原生的 identity屬性完成。

所以本期就來探討這樣的問題,分析一下利弊給各位做個參考。

 

自行產生連續的數字欄位

首先,先解釋一下,在 SQL Server中, identity為何被稱為「識別屬性」而不是稱為「自動編號」(雖然它的本體是在做自動編號),主要是當我們在設計資料庫的時後,必需指定一個主索引鍵,也就是 Primary key,而這個 primary key必需有幾個條件,最基本的條件是不能重複,其次是容易取得。

而大多數的人,都以為 primary key只要不重複就好,事實上容易取得也相當重要,所以很多時後在設計資料庫時不是只要關心不重複即可,適時的使用替代鍵也是相當重要的,而 identity就是用來這個時後,它不會重複而且它不需要輸入值,它由系統自動產生,所以如果你是要以 identity識別屬性來取代原本的 primary key,是非常建議的。

既然是識別屬性,所以它並不會幫你維護數字的「連續性」,換句話說,如果你有五筆資料,分別有1、2、3、4、5這幾個識別屬性,當你把3這筆資料刪除後,再新增一筆資料,最後的結果會是出現6這個識別屬性值,在實務上也不會要求資料的「連續性」,因為識別屬性原本就不是在處理這方面的事情。

如果不用識別屬性,我們還有哪些選擇?我來介紹一下,順便實際測試就知道有多慘烈。

不過在測試之前,我們先將該設定的資料表、環境先產生出來:

image

建立一個資料表名為 table1,設定 a的資料型別為整數,先不設定 identity,之後隨時視情境更改。


新增資料時取出最大值,再將值加1填入

撰寫一個 stored procedure,程式內容是輸入 N筆(參數),然後每新增一筆,就從現行資料表中取出最大值加一寫回,並且記錄起始時間與結束時間好計算所需時間,在一開始甚至設定開始新增資料的時間,程式如下:

image image

先用單一連線測試這隻程式,輸入下面語法:
exec usp_ins_table1_maxvalue 3000,'session1'

一次輸入3000筆資料,看要花費多久時間,結果如下:

image

總共花了301秒。

接下來,開三個連線,分別執行下列程式:

exec usp_ins_table1_maxvalue 1000,'session1'
exec usp_ins_table1_maxvalue 1000,'session2'
exec usp_ins_table1_maxvalue 1000,'session3'
最後得到的時間為下:

image

三個連線的執行時間都是相同,不過我們來看,若用此種做法,會不會有資料重複的問題,所以我們使用下面的語法:
select a from table1
group by a

結果得到如下的結果:

image

好的!也就是說,會有重複的值會被產生出來(其實這個原本就是可預料到的),再進一步的用這個指令去分析,哪個連線的值造成重複:

select * from (
select count(*) as counts ,a from table1
group by a
having count(*) >1) as b
inner join table1 as c
on b.a = c.a

會得到如下的結果:

image

是的,有兩個連線同時取得了最大值,再將這個最大值加一填入,就會變成多個連線會取出相同值的結果,其實一點也不意外。

為此,我們把資料表改成如下:

image

是的,也就是把a欄位改成是 identity,再測試一次(當然 stored procedure也要修改,因為a這個欄位無需自行輸入):

結果再用 group by的句法檢查資料是否有重複,得到的情況如下:

image

很好,如此一來,使用 identity資料就不會重複了,那麼只要把原來的 stored procedure改成如下的寫法,你就可以自行控制客制化的自動編號了:
insert table1 (b,c) values (format(@maxvalue,N'\C00000'),@from)

format是 SQL Server 2012的新函數,可以利用它產生你想要的自訂自動編號,最後得到的結果如下:

image

所以結論是,如果要客制化文字版本的自動編號,記得此種 MAX的手法還是請你乖乖的建立一個 identity欄位吧!

用一個資料表記錄目前連續編號的最大值

第二種做法,是在一個資料表記錄目前此資料表的自動編號最大值,取得之後加1將資料新增,再利用 update的方式修改回該資料表。
為了達到設定目的,同樣的我們也是準備一個資料表,記錄目前連續編號的最大值,如下:

image

目前資料表 sequence_table,記錄目前的最大值;當然,打從心裡我就不是很支持這樣的做法,因為它一定也會面臨和剛才 maxvalue一樣的問題,也就是同樣會取得重複值,不過還真的有資料庫系統是這麼做的,例如sqlite這個資料庫,當你設定「自動編號」時,它就會幫你產生一個資料表,用來記錄資料連續的狀況,如下圖:

image

上圖是 sqlite的管理界面,其中有一個資料表用來記錄每個資料表的連續編號最大值。
不過各位不要忘記,sqlite這個資料庫用在像 iphone這樣的行動裝置上,同一時間是不會有別人同時新增資料的,所以如果你想自己維護,勢必也會發生問題。
不過還是來試看看結果如何,我們把預存程序做一下修改如下:

image image

接著,先用一個 session測試一下,發現所花的時間沒什麼變,同樣是差不多數據的303秒(新增3000筆),接下來用三個 session同時新增資料,執行結果出乎意料的非常悲劇,如下圖:

image

是的,你沒有看錯,幾乎完完全全的重複了,各新增1000筆,結果用 group by檢測結果竟然只剩1000筆,表示每一個數值都有重複到,簡直是慘中之慘,算是完全不可行的方案。
當然,如果你硬要用 try catch的方式改寫,並且設定主索引鍵以避免資料重複,也不是不行,我們就姑且試一下吧,把預存程序改成如下的寫法:

image

利用 try catch的方式,如果有新增錯誤,則跳到 catch的地方執行。
最後結果確實資料沒有重複,但是每個連線新增1000筆的速度比100秒還要慢,如下圖:

image

大概是一般處理的6倍左右的速度,這樣的速度說實在實在是不能看,因為在過程之中產生了一堆的重複值而跑到 try catch的地方執行了,如下:

image

所以各位不要使用這種做法。

使用觸發程序產生



最後一種做法是使用預存程序的方式,我們還是把環境先設定一下,如下:

image

我們利用 trigger的方式,當資料表新增時,由 trigger透過 identity來產生自動編號值:

image 

結果發現,所需的時間差不多,也要300秒的時間。
接下來,我們還是分三個連線新增資料,看看結果會如何。

結果三個連線中,有兩個連線會出現 dead lock,如下:

image

至於新增進來的資料筆數,也並不是3000筆,而是只有1000筆,更離奇的是,資料竟然不連續,如下:

image

好吧…為了解決死鎖的問題,還是得動用 try catch,我們將預存程序的程式碼改一下,變成如下:

image

這樣測試後的結果,dead lock確實是解決了,資料也確實不重複,但是筆數確只有2963筆,而且資料一樣不連續,如下圖:

image

image

至於時間呢?每個連線大約花150秒才將資料填入,比第一種 max搭配 identity的方式還要慢50秒。

 

結論


由以上的實驗,我們可以發現,如果你想要產生一個文字的連續不重複號碼,在兼顧效能以及資料不重複的特性,採用 max函數,搭配 identity是目前實測中最快,以及最簡單的撰寫方式,請各位可以自行嘗試!

image

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List