Managed ODP.NET 讓 .NET 程式擺脫對 Oracle Client 的依賴, 對常被 11.2 或是 12.1?32 或 64 bit 等版本鬼問題糾纏過的人就知道這點有多好, 故除非專案有不得已的限制,Managed ODP.NET 成為我寫 Oracle 相關 .NET 程式的唯一選擇。

但不幸地,跟 Unmanaged ODP.NET 相比,Managed ODP.NET 還少了一些功能(參考:官方有張Managed ODP.NET 不支援項目清單) 其中讓我最有感的是少了讓大量 INSERT 加速 160 倍的 OracleBulkCopy, 實際作業免不了遇上需要寫入數萬筆資料的場合,用 Dapper 跑 INSERT 配合參數物件批次寫入, 等上三五分鐘是家常便飯,此時若講求速度,只能回頭用 Unmanaged ODP.NET + OracleBulkCopy。

今天再遇類似問題,被我發現媲美 OracleBulkCopy 的解決方案。

用實例來說明,以下是我專案裡的共用程式庫,它能讀入 XML 資料檔,先轉成 JSON 再反列人為強型別物件陣列, 利用 System.Reflection 自動產生 INSERT 語法並搭配 Dapper Execute(), 將資料分成多個批次寫入 Oracle 資料庫。 (延伸閱讀:程式範例 - 正式台測試台 JSON + Dapper 資料搬移術)

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using Newtonsoft.Json;
using System.IO;
using System.Diagnostics;

public class DataHelper
{
    public static string CnStr = null;

    const int BATCH_SIZE = 1024;
    static IEnumerable<T[]> SplitBatch<T>(IEnumerable<T> items, int batchSize)
    {
        return items.Select((item, idx) => new { item, idx })
            .GroupBy(o => o.idx / batchSize)
            .Select(o => o.Select(p => p.item).ToArray());
    }

    public static void ImportToOra<T>(string xmlPath)
    {
        var ds = new DataSet();
        ds.ReadXml(xmlPath, XmlReadMode.Auto);
        var json = JsonConvert.SerializeObject(ds.Tables[0], Formatting.Indented);
        var data = JsonConvert.DeserializeObject<T[]>(json);

        var tableName = typeof(T).Name.ToUpper();

        var propNames = typeof(T)
            .GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance)
            .Select(o => o.Name).ToArray();
        string insertSql = 
        $"INSERT INTO {tableName} ({string.Join(",", propNames)}) VALUES ({string.Join(",", propNames.Select(o => $":{o}").ToArray())})";

        using (var cn = new OracleConnection(CnStr))
        {
            cn.Open();
            Console.WriteLine($"清除資料內容 - {tableName}");
            cn.Execute($"DELETE FROM {tableName}");

            var sw = new Stopwatch();
            sw.Start();
            Console.WriteLine($"開始寫入資料 - {tableName}...");
            var count = 0;
            foreach (var batchData in SplitBatch<T>(data, BATCH_SIZE))
            {
                count += batchData.Length;
                cn.Execute(insertSql, batchData);
                Console.Write($"\r{count}/{data.Length}({count * 1.0 / data.Length:p0})");
            }
            Console.WriteLine();
            sw.Stop();
            Console.WriteLine($"資料寫入完成 - {sw.ElapsedMilliseconds:n0}ms");
        }
    }
}

這做法還蠻酷的,為 XML 定義好強型別物件,就能自動產生 INSERT 語法並匯入資料庫,唯一的缺點是速度不夠快。手邊有批實際資料,實測結果如下:

清除資料內容 - TABLE1
開始寫入資料 - TABLE1...
27152/27152(100%)
資料寫入完成 - 96,397ms
清除資料內容 - TABLE2
開始寫入資料 - TABLE2...
37675/37675(100%)
資料寫入完成 - 133,408ms
清除資料內容 - TABLE3
開始寫入資料 - TABLE3...
6228/6228(100%)
資料寫入完成 - 22,394ms
清除資料內容 - TABLE4
開始寫入資料 - TABLE4...
6228/6228(100%)
資料寫入完成 - 20,513ms

四個資料表近 8 萬資料耗費 272 秒,約等上四分半鐘。

Stackoverflow 學到新招 - ODP.NET Array Binding

Array Binding 的原理是依一般方式宣告 OracleParameter,設定 String、DataTime 等資料型別, 但提供 Value 時改傳入 string[] 或 DateTime[] 等陣列(當然,筆數必須一致),執行前設定 OracleCommand.ArrayBindCount = 陣列長度, 如此可在一次 Oracle 呼叫動作寫入數百上千筆資料,比起一筆筆 INSERT 快上 N 倍。以下是個簡單範例:

using (var cn = new OracleConnection(cnStr))
{
    cn.Open();

    var pNum = new OracleParameter("Num", OracleDbType.Int32);
    pNum.Value = new int[] { 1, 2, 3 };

    var pStr = new OracleParameter("Str", OracleDbType.Varchar2);
    pStr.Value = new string[] { "A", "B", "C" };

    OracleCommand cmd = con.CreateCommand();
    cmd.CommandText = "INSERT INTO T (Num, Str) VALUES (:Num, :Str)";
    cmd.ArrayBindCount = pNum.Length;
    cmd.BindByName = true;
    cmd.Parameters.Add(pNum);
    cmd.Parameters.Add(pStr);
    cmd.ExecuteNonQuery();
}

回到我的程式上,由於要支援任意強型別物件,故 OracleParameter 型別與值陣列需動態產生, 這用 System.Relection 的 PropertyInfo、PropertyInfo.GetValue() 不難解決,多寫個幾行程式即可搞定。 (我的案例屬動態操作,如果是已知型別的單純應用不必搞到這麼複雜, 用 LINQ 寫成 pColName.Value = dataCollection.Select(o => o => ColName).ToArray(); 即可,不用費腦)

最後,改用 Array Binding 的程式碼版本長這樣:

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using Newtonsoft.Json;
using System.IO;
using System.Diagnostics;

namespace MDBXmlDownloader
{
    public class DataHelper
    {
        public static string CnStr = null;

        const int BATCH_SIZE = 1024;
        static IEnumerable<T[]> SplitBatch<T>(IEnumerable<T> items, int batchSize)
        {
            return items.Select((item, idx) => new { item, idx })
                .GroupBy(o => o.idx / batchSize)
                .Select(o => o.Select(p => p.item).ToArray());
        }

        static void InsertWithArrayBinding<T>(OracleCommand cmd, 
            Dictionary<string, OracleParameter> oraParams, 
            Dictionary<string, PropertyInfo> props, 
            T[] data)
        {
            cmd.ArrayBindCount = data.Length;
            cmd.Parameters.Clear();
            foreach (var pn in oraParams.Keys)
            {
                var p = oraParams[pn];
                p.Value = data.Select(o => props[pn].GetValue(o)).ToArray();
                cmd.Parameters.Add(p);
            }
            cmd.ExecuteNonQuery();
        }

        public static void ImportToOra<T>(string xmlPath)
        {
            var ds = new DataSet();
            ds.ReadXml(xmlPath, XmlReadMode.Auto);
            var json = JsonConvert.SerializeObject(ds.Tables[0], Formatting.Indented);
            var data = JsonConvert.DeserializeObject<T[]>(json);

            var tableName = typeof(T).Name.ToUpper();

            var props = typeof(T)
                .GetProperties(BindingFlags.Public | BindingFlags.DeclaredOnly | BindingFlags.Instance);
            var propNames = props.Select(o => o.Name).ToArray();
            var propDict = props.ToDictionary(o => o.Name, o => o);
            var oraParams = props.ToDictionary(o => o.Name, o => {
                var p = new OracleParameter();
                p.ParameterName = o.Name;
                switch (o.PropertyType.ToString().Split('.').Last().TrimEnd(']'))
                {
                    case "String":
                        p.DbType = DbType.String;
                        break;
                    case "DateTime":
                        p.DbType = DbType.DateTime;
                        break;
                    default:
                        throw new NotImplementedException(o.PropertyType.ToString());
                }
                return p;
            });
            string insertSql = 
            $"INSERT INTO {tableName} ({string.Join(",", propNames)}) VALUES ({string.Join(",", propNames.Select(o => $":{o}").ToArray())})";

            using (var cn = new OracleConnection(CnStr))
            {
                cn.Open();
                Console.WriteLine($"清除資料內容 - {tableName}");
                cn.Execute($"DELETE FROM {tableName}");

                var cmd = cn.CreateCommand();
                cmd.BindByName = true;
                cmd.CommandText = insertSql;

                var sw = new Stopwatch();
                sw.Start();
                Console.WriteLine($"開始寫入資料 - {tableName}...");
                var count = 0;
                foreach (var batchData in SplitBatch<T>(data, BATCH_SIZE))
                {
                    count += batchData.Length;
                    //cn.Execute(insertSql, batchData);
                    InsertWithArrayBinding<T>(cmd, oraParams, propDict, batchData);
                    Console.Write($"\r{count}/{data.Length}({count * 1.0 / data.Length:p0})");
                }
                Console.WriteLine();
                sw.Stop();
                Console.WriteLine($"資料寫入完成 - {sw.ElapsedMilliseconds:n0}ms");
            }
        }
    }
}

迫不及用相同的資料實測一次,結果如下:

清除資料內容 - TABLE1
開始寫入資料 - TABLE1...
27152/27152(100%)
資料寫入完成 - 1,692ms
清除資料內容 - TABLE2
開始寫入資料 - TABLE2...
37675/37675(100%)
資料寫入完成 - 2,309ms
清除資料內容 - TABLE3
開始寫入資料 - TABLE3...
6228/6228(100%)
資料寫入完成 - 478ms
清除資料內容 - TABLE4
開始寫入資料 - TABLE4...
6228/6228(100%)
資料寫入完成 - 343ms

Wow! 一樣約 8 萬筆資料,Array Binding 只花了 5 秒,比 Dapper 陣列 INSERT 快 50 倍以上。

有了這件新武器,未來遇到需要高速寫入大量資料的場合,就不用走回頭路改用 Unmanaged ODP.NET 囉~

The Managed ODP.NET doesn't support OracleBulkCopy, but we can batch insert large amount data quickly with array binding.


Comments

# by Shih Hung

之前的專案有用到過OracleDataAdapter的批次寫入功能,發現效能也不慎理想,甚至在加大Size時還有可能頂到Oracle限制的65536的變數上限。 OracleDataAdapter.UpdateCommand = OracleDataAdapter.InsertCommand; OracleDataAdapter.UpdateBatchSize = 30; OracleDataAdapter.Update(DataTable); 不知道黑大目前這個做法,會不會頂到這個上限值呢?

# by Jeffrey

to Shih Hung, 追了原始碼,ODP.NET 的 Update(DataTable) 方法背後也是用 ArrayBinding,應該比跑迴圈快很多,好奇效能不理想是和迴圈法比較嗎? 用批次一定會有上限,我的話大概就是實測調到可接受範圍就好。

# by Shih Hung

不理想的情況是與OracleBulkCopy做比較的,目前也是改用黑大的方案實作,但是神奇的是OracleDataAdapter的作法在oracle資料庫端會短時間內產生很多的動態變數,但用這個方法卻不會產生(我們公司DBA監控告知的)。所以我目前也是設定1024當閥值了。

# by Andy

Managed ODP.NET在19.10版本終於支援了OracleBulkCopy! https://medium.com/@alex.keh/announcing-odp-net-19-10-release-new-net-5-and-bulk-copy-support-be6f395155c9 不過我自己測試Managed組件的OracleBulkCopy遇到中文會有亂碼,換回原本的Unmanaged組件就完全沒有問題。

Post a comment