ODP.NET OracleBulkCopy
3 |
嫌棄使用OracleCommand批次更新大量資料效能不佳,爬文找到ODP.NET有個OracleBulkCopy類別支援將整個ADO.NET DataTable一次送至資料庫更新(與SQL 2008的Table Value Parameter有異曲同工之妙),於是做了Lab測試效果。
在Oracle資料庫建了一個TABLE BIGONE (N DECIMAL(6,0), T NVARCHAR2(64))資料表,在.NET程式中產生五萬筆資料,分別使用以下兩種方法寫入資料庫:
1) 建立OracleCommand,宣告OracleParameter以迴圈指定參數值執行五萬次ExecuteNonQuery()2) 建立Schema相同的DataTable,塞入五萬筆DataRow後執行OracleBulkCopy.WriteToServer()
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using System.Diagnostics;
using System.Data;
namespace BulkInsert
{
class Program
{
static string cs = "Data Source=ORA;User Id=O;Password=P;";
//Table Schema =
//CREATE TABLE BIGONE (N DECIMAL(6,0), T NVARCHAR2(64))
const int COUNT = 50000;
static void Main(string[] args)
{
var data = new Dictionary<int, string>();
for (int i = 0; i < COUNT; i++)
data.Add(i, string.Format("INFO{0:00000000}", i));
CleanTable();
TestCommand(data);
CleanTable();
TestBulkCopy(data);
Console.Read();
}
static void CleanTable()
{
using (var cn = new OracleConnection(cs))
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText = "TRUNCATE TABLE BIGONE";
cmd.ExecuteNonQuery();
}
}
static void TestCommand(Dictionary<int, string> data)
{
Stopwatch sw = new Stopwatch();
sw.Start();
using (var cn = new OracleConnection(cs))
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText =
"INSERT INTO BIGONE (N, T) VALUES (:n, :t)";
var pN = cmd.Parameters.Add("n", OracleDbType.Decimal);
var pT = cmd.Parameters.Add("t", OracleDbType.NVarchar2);
foreach (int k in data.Keys)
{
pN.Value = k;
pT.Value = data[k];
cmd.ExecuteNonQuery();
}
cn.Close();
}
sw.Stop();
Console.WriteLine("TestCommand: {0:N0}ms", sw.ElapsedMilliseconds);
}
static void TestBulkCopy(Dictionary<int, string> data)
{
Stopwatch sw = new Stopwatch();
sw.Start();
using (var cn = new OracleConnection(cs))
{
cn.Open();
var cmd = cn.CreateCommand();
cmd.CommandText="SELECT * FROM BIGONE WHERE 1=0";
DataTable t = new DataTable();
var dr = cmd.ExecuteReader();
//create the DataTable object according to Oracle table
t.Load(dr);
dr.Close();
//fill the DataTable
foreach (int k in data.Keys)
t.Rows.Add(k, data[k]);
//Bulk Copy!
OracleBulkCopy bc = new OracleBulkCopy(cn);
bc.DestinationTableName="BIGONE";
bc.WriteToServer(t);
bc.Close();
cn.Close();
}
sw.Stop();
Console.WriteLine("TestBulkCopy: {0:N0}ms", sw.ElapsedMilliseconds);
}
}
}
TestCommand: 76,334ms
TestBulkCopy: 472ms
測試結果懸殊,OracleBulkCopy比迴圈執行OracleCommand快了160倍以上。
兩點注意事項:
- OracleBulkCopy是ODP.NET才有的功能,System.Data.OracleClient無此類別。
- 如同一般Bulk Copy作業的限制,OracleBulkCopy不支援Transaction,無法Rollback。(參考)
Comments
# by LSK
這個之前也有找到,有拿來用過 速度還不錯 可惜當該TABLE有頻繁的被存取時 會Bulk Copy失敗,黑大可以試看看
# by te2y
ありがとうございます。
# by w
https://www.modb.pro/db/23553 能不用还是别用了,估计这也是官方在nuget中Oracle.ManagedDataAccess没添加此函数的原因 OracleBulkCopy 没有唯一性判断,破坏主键,不支持事务,不生成redo。 重建索引,删除重复数据都无效,只能删除、重建表。