實戰手記：讓百萬級資料瞬間匯入SQL Server

想必每個DBA都喜歡挑戰資料匯入時間，用時越短工作效率越高，也充分的能夠證明自己的實力。實際工作中有時候需要把大量資料匯入資料庫，然後用於各種程式計算，本文將向大家推薦一個挑戰4秒極限讓百萬級資料瞬間匯入SQL Server實驗案例。

本實驗將使用5中方法完成這個過程，並詳細記錄各種方法所耗費的時間。所用到工具為Visual Studio 2008和SQL Server
2000、SQL Server 2008，分別使用5中方法將100萬條資料匯入SQL Server 2000與SQL Server
2008中，實驗環境是DELL 2850雙2.0GCPU，2G記憶體的伺服器。感興趣的朋友可以下載原始碼自己驗證一下所用時間。

好了，下面我們分別使用基本的Insert 語句、使用BULK INSERT語句、在多線程中使用BULK
INSERT、使用SqlBulkCopy類、在多線程中使用SqlBulkCopy類五種方法，挑戰4秒極限。還要有一點需要進行說明，本實驗中執行
SQL語句的地方使用了IsLine
FrameWork架構中的DataProvider模組，這個模組只是對SQL配置的讀取和封裝，並不會對最終結果有本質性的影響，關於IsLine
FrameWork架構方面的知識，請參考“IsLine FrameWork”架構系列文章。

資料庫方面使用SQL Server 2000與SQL Server 2008，表名TableB，欄位名稱為Value1，資料庫名可以在App.config中修改，預設為test。

方法一.使用基本的Insert 語句

這種方法是最基本的方法，大多數人一開始都會想到這種方法。但是Insert語句似乎並不適合大批量的操作，是不是這樣呢?

本方法中將100萬資料分為10個批次，每個批次10萬條，每10萬條1個事務，分10次匯入資料庫。

-->基本語句:

Insert Into TableB (Value1) values (‘”+i+”’); 說明：語句中的i是宿主程式中的一個累加變數，用於填充資料庫欄位中的值。

SQL Server 2000 耗時：901599

SQL Server 2008耗時：497638

方法二.使用BULK INSERT語句

這個類的效果，在本實驗中可以說是最令人滿意的了，它的使用最簡便、靈活，速度很快。

“BULK INSERT”語句似乎不是很常用，
Aicken聽說Oracle中有一種可以將外部檔案對應為Oracle暫存資料表，然後直接將暫存資料表中的資料匯入Oracle其他表中的方法，這種方法的速
度非常令人滿意，SQL SERVER的BULK INSERT是不是同樣令人滿意呢?

-->　基本語句：

BULK INSERT TableB FROM '

c:\\sql.txt' WITH (FIELDTERMINATOR = ',',ROWTER

/.,mbMINATOR='|',BATCHSIZE = 100000)

說明：“c:\\sql.txt”是一個預先產生的包含100條資料的檔案，這些資料以“|”符號分隔，每10萬條資料一個事務。

SQL Server 2000耗時：4009

SQL Server 2008耗時：10722

方法三.在多線程中使用BULK INSERT

在方法二的基礎上，將100萬條資料分五個線程，每個線程負責20萬條資料，每5萬條一個事物，五個線程同時啟動，看看這樣的效果吧。

SQL Server 2000耗時：21099

SQL Server 2008耗時：10997

方法四.使用SqlBulkCopy類

這種方法速度也很快，但是要依賴記憶體，對於幾千萬條、多欄位的複雜資料，可能在記憶體方面會有較大的消耗，不過可以使用64位解決方案處理這個問題。

幾千萬條、多欄位的資料的情況一般在一些業務情境中會遇到，比如計算全球消費者某個業務周期消費額時，要先獲得主要資料庫表中的會員消費記錄快照，並
將快照儲存至暫存資料表中，然後供計算程式使用這些資料。並且有些時候消費者的消費資料並不在一台資料庫伺服器中，而是來自多個國家的多台伺服器，這樣我們就
必須藉助記憶體或外存裝置中轉這些資料，然後清洗、合并、檢測，最後匯入專用表供計算程式使用。

基本語句：

using (System.Data.SqlClient.SqlBulkCopy sqlBC

= new System.Data.SqlClient.SqlBulkCopy(conn))

{ sqlBC.BatchSize = 100000; sqlBC.BulkCopyTimeout

= 60; sqlBC.DestinationTableName = "dbo.TableB";

sqlBC.ColumnMappings.Add("valueA", "Value1");

sqlBC.WriteToServer(dt); }

說明：

BatchSize = 100000; 指示每10萬條一個事務並提交

BulkCopyTimeout = 60; 指示60秒按逾時處理

DestinationTableName = "dbo.TableB"; 指示將資料匯入TableB表

ColumnMappings.Add("valueA", "Value1"); 指示將記憶體中valueA欄位與TableB中的Value1欄位匹配

WriteToServer(dt);寫入資料庫。其中dt是預先構建好的DataTable，其中包含valueA欄位。

SQL Server 2000耗時：4989

SQL Server 2008耗時：10412

方法五.在多線程中使用SqlBulkCopy類

基於方法四，將100萬條資料分五個線程，每個線程負責20萬條資料，每5萬條一個事物，五個線程同時啟動，看看這樣的效果吧。

SQL 2000耗時：7682

SQL 2008耗時：10870

結果

幾天的時間終於把這個實驗給完成了，比較令人失望的是SQL SERVER 2008匯入資料的效能似乎並不想我們想象的那樣優秀。