極限挑戰—C#+ODP 100萬條資料匯入Oracle資料庫僅用不到1秒

文章目錄

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

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

    上一次文章中提及的實驗：

    極限挑戰—C#100萬條資料匯入SQL SERVER資料庫僅用4秒 (附源碼)

    http://www.cnblogs.com/isline/archive/2010/03/18/1688783.html

    這個實驗是針對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	20
3000	3749	113
4000	5737	40
5000	6820	52
6000	9469	72
7000	10226	69
8000	15280	123
9000	11475	83
10000	14536	121
11000	15705	130
12000	16548	145
13000	18765	125
14000	20393	116
15000	22181	159

 因為篇幅原因，不再粘貼全部的資料，但是我們可以看一下由此資料產生的散佈圖：

 

    其中有些資料有些跳躍，可能和資料庫本身有關係，但是大部分資料已經能說明問題了。看了這些資料後，是不是有些心動了？

    來源程式放了一段時間直接拷貝貼過來了，可能需要調試一下才能跑通，不過不是本質性問題，對了如果要測試別忘記安裝Oracle訪問組件。