oracle odp arraybindcount 极限,極限挑戰—C#+ODP 100萬條數據導入Oracle數據庫僅用不到1秒...

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

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

這個試驗是針對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訪問組件。