程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 100萬條數據導入SQL SERVER數據庫僅用4秒

100萬條數據導入SQL SERVER數據庫僅用4秒

編輯:關於SqlServer

  實際工作中有時候需要把大量數據導入數據庫,然後用於各種程序計算,本實驗將使用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


  以上就是這幾天的實驗結果了,比較令人失望的是SQL Server 2008導入數據的性能似乎並不想我們想象的那樣優秀。

  另外,有下載源代碼的幫我看看,為什麼多線程的成績還不如單線程的優秀呢?是靜態資源使用不當造成的,還是其他什麼原因?

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

  測試數據下載地址:http://files.cnblogs.com/isline/Data.rar

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved