詳解如何挑戰4秒內百萬級資料匯入SQL Server

對於大資料量的匯入，是DBA們經常會碰到的問題，在這裡我們討論的是SQL Server環境下百萬級資料量的匯入，希望對大家有所協助。51CTO編輯向您推薦《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記憶體的伺服器。感興趣的朋友可以下載原始碼自己驗證一下所用時間。

還要有一點需要進行說明，本實驗中執行SQL語句的地方使用了IsLine
FrameWork架構中的DataProvider模組，這個模組只是對SQL配置的讀取和封裝，並不會對最終結果有本質性的影響，關於IsLine
FrameWork架構方面的知識，請參考“IsLine FrameWork”架構系列文章。

下面進入正題，分別使用基本的Insert 語句、使用BULK INSERT語句、在多線程中使用BULK INSERT、使用SqlBulkCopy類、在多線程中使用SqlBulkCopy類五種方法，挑戰4秒極限。

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

圖1 實驗中的5種方法

方法一.使用基本的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 2000	SQL 2008
基本Insert Into	901599	497638
單線程Bulk Insert	4209	10722
多線程Bulk Insert	21099	10997
單線程SqlBulkCopy	4989	10412
多線程SqlBulkCopy	7682	10870

以上就是這幾天的實驗結果了，比較令人失望的是SQL SERVER 2008匯入資料的效能似乎並不想我們想象的那樣優秀。

源碼地址：http://files.cnblogs.com/isline/sqltest.rar

測試資料：http://files.cnblogs.com/isline/Data.rar