C#100萬條數據導入SQL SERVER數據庫僅用4秒 (附源碼)

作者: Aicken(李鳴) 來源: 博客園發布時間: 2010-09-08 15:00 閱讀: 16183 次推薦: 3 原文鏈接 [收藏]

摘要：實際工作中有時候需要把大量數據導入數據庫，然后用于各種程序計算，本實驗將使用5中方法完成這個過程，并詳細記錄各種方法所耗費的時間。

　　本實驗中所用到工具為VS2008和SQL SERVER 2000、SQL SERVER 2008，分別使用5中方法將100萬條數據導入SQL 2000與SQL 2008中，實驗環境是DELL 2850雙2.0GCPU，2G內存的服務器。感興趣的朋友可以下載源代碼自己驗證一下所用時間。

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

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

　　數據庫方面使用SQL 2000與SQL 2008，表名TableB，字段名稱為Value1，數據庫名可以在App.config中修改，默認為test。

圖 1 試驗中的5種方法

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

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

　　本方法中將100萬數據分為10個批次，每個批次10萬條，每10萬條1個事務，分10次導入數據庫。

　　基本語句：Insert Into TableB (Value1) values (‘”+i+”’);

　　說明：語句中的i是宿主程序中的一個累加變量，用于填充數據庫字段中的值。

　　SQL 2000 耗時：901599

　　SQL 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 2000耗時：4009

　　SQL 2008耗時：10722

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

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

　　SQL 2000耗時：21099

　　SQL 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 2000耗時：4989

　　SQL 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