date time comments 3 pageviews

嫌棄使用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。 重建索引，删除重复数据都无效，只能删除、重建表。

Post a comment