讓百萬級數據瞬間導入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導入數據的性能似乎并不想我們想象的那樣優秀。
關鍵字:SQL Server、服務器、數據庫
新文章:
- CentOS7下圖形配置網絡的方法
- CentOS 7如何添加刪除用戶
- 如何解決centos7雙系統后丟失windows啟動項
- CentOS單網卡如何批量添加不同IP段
- CentOS下iconv命令的介紹
- Centos7 SSH密鑰登陸及密碼密鑰雙重驗證詳解
- CentOS 7.1添加刪除用戶的方法
- CentOS查找/掃描局域網打印機IP講解
- CentOS7使用hostapd實現無AP模式的詳解
- su命令不能切換root的解決方法
- 解決VMware下CentOS7網絡重啟出錯
- 解決Centos7雙系統后丟失windows啟動項
- CentOS下如何避免文件覆蓋
- CentOS7和CentOS6系統有什么不同呢
- Centos 6.6默認iptable規則詳解