Oracle大量匯入100萬條資料不到1秒____Oracle

 .Net程式中可以通過ODP調用特性，對Oracle資料庫進行操作，今天來講一下資料批量插入的功能，所用技術不高不深，相信很多朋友都接觸過，小弟班門弄斧了，呵呵。這篇文章是上篇文章的續集，因為上一次實驗的徵集結果沒有突破4秒的方法，所以這次繼續挑戰與挖掘新方法，雖然是Oracle，但仍具有一定收藏意義。

    上一次文章中提及的實驗： SqlServer大量匯入C#100萬條資料僅4秒附源碼 http://blog.csdn.net/mrobama/article/details/53813084

這個實驗是針對SQL SERVER資料庫的，宿主環境也是.Net，有興趣的朋友可以將這兩個實驗對比一下，為日後工作批量導數提供支援。

    另外，一些朋友對上次實驗環境有些異議，認為應該對資料庫和伺服器做最佳化或設定，以體現實驗最終的時間結果。這個固然會影響實驗的時間結果，但考慮到在實驗環境中，對資料庫最佳化的標準與最佳化程度不便統一與定量，實驗結果也不易說明其影響源，所以這次實驗依然以標準資料庫建庫後的配置為主，實驗所在伺服器硬體環境與上次實驗保持一致。實驗目的在於挖掘、對比宿主程式中的資料大量操作方法。

    有新方法提升效能時間指標的朋友，歡迎互相切磋，互相提高，嘴上功夫就免了。。。

    好了本文開始。

    ● 普通肉墊式

    什麼叫批量插入呢，就是一次性插入一批資料，我們可以把這批資料理解為一個大的數組，而這些全部只通過一個SQL來實現，而在傳統方式下，需要調用很多次的SQL才可以完成，這就是著名的“數組綁定”的功能。我們先來看一下傳統方式下，插入多行記錄的操作方式：

  代碼

// 設定一個資料庫的串連串， string connectStr = " User Id=scott;Password=tiger;Data Source= " ; OracleConnection conn = new OracleConnection(connectStr); OracleCommand command = new OracleCommand(); command.Connection = conn; conn.Open(); Stopwatch sw = new Stopwatch(); sw.Start(); // 通過迴圈寫入大量的資料，這種方法顯然是肉墊 for ( int i = 0 ; i < recc; i ++ ) { string sql = " insert into dept values( " + i.ToString() + " , " + i.ToString() + " , " + i.ToString() + " ) " ; command.CommandText = sql; command.ExecuteNonQuery(); } sw.Stop(); System.Diagnostics.Debug.WriteLine( " 普通插入: " + recc.ToString() + " 所佔時間: " + sw.ElapsedMilliseconds.ToString());

 

 

    我們先準備好程式，但是先不做時間的測定，因為在後面我們會用多次迴圈的方式來計算所佔用的時間。

    ● 使用ODP特性

    看上面的程式，大家都很熟悉，因為它沒有用到任何ODP的特性，而緊接著我們就要來介紹一個神奇的程式了，我們看一下代碼，為了更直觀，我把所有的注釋及說明直接寫在代碼裡：

 

// 設定一個資料庫的串連串 string connectStr = " User Id=scott;Password=tiger;Data Source= " ; OracleConnection conn = new OracleConnection(connectStr); OracleCommand command = new OracleCommand(); command.Connection = conn; // 到此為止，還都是我們熟悉的代碼，下面就要開始嘍 // 這個參數需要指定每次批插入的記錄數 command.ArrayBindCount = recc; // 在這個命令列中,用到了參數,參數我們很熟悉,但是這個參數在傳值的時候 // 用到的是數組,而不是單個的值,這就是它獨特的地方 command.CommandText = " insert into dept values(:deptno, :deptname, :loc) " ; conn.Open(); // 下面定義幾個數組,分別表示三個欄位,數組的長度由參數直接給出 int [] deptNo = new int [recc]; string [] dname = new string [recc]; string [] loc = new string [recc]; // 為了傳遞參數,不可避免的要使用參數,下面會連續定義三個 // 從名稱可以直接看出每個參數的含義,不在每個解釋了 OracleParameter deptNoParam = new OracleParameter( " deptno " , OracleDbType.Int32); deptNoParam.Direction = ParameterDirection.Input; deptNoParam.Value = deptNo; command.Parameters.Add(deptNoParam); OracleParameter deptNameParam = new OracleParameter( " deptname " , OracleDbType.Varchar2); deptNameParam.Direction = ParameterDirection.Input; deptNameParam.Value = dname; command.Parameters.Add(deptNameParam); OracleParameter deptLocParam = new OracleParameter( " loc " , OracleDbType.Varchar2); deptLocParam.Direction = ParameterDirection.Input; deptLocParam.Value = loc; command.Parameters.Add(deptLocParam); Stopwatch sw = new Stopwatch(); sw.Start(); // 在下面的迴圈中,先把數組定義好,而不是像上面那樣直接產生SQL for ( int i = 0 ; i < recc; i ++ ) { deptNo[i] = i; dname[i] = i.ToString(); loc[i] = i.ToString(); } // 這個調用將把參數數組傳進SQL,同時寫入資料庫 command.ExecuteNonQuery(); sw.Stop(); System.Diagnostics.Debug.WriteLine( " 批量插入: " + recc.ToString() + " 所佔時間: " + sw.ElapsedMilliseconds.ToString());

 

 

    以上代碼略顯冗長，但是加上注釋後基本也就表達清楚了。

    好了，到目前為止，兩種方式的插入操作程式已經完成，就剩下對比了。我在主函數處寫了一個小函數，迴圈多次對兩個方法進行調用，並且同時記錄下時間，對比函數如下：

 

for ( int i = 1 ; i <= 50 ; i ++ ) { Truncate(); OrdinaryInsert(i * 1000 ); Truncate(); BatchInsert(i * 1000 ); }

 

 

    當資料量達到100萬層級時，所用時間依然令人滿意，最快一次達到890毫秒，一般為1秒左右。     經過實驗，得出一組資料，可以看出兩種方式在效率方面驚人的差距（佔用時間的單位為毫秒），部分資料如下：

記錄數	標準	批處理
1000	1545	29
2000	3514