Managed ODP.NET 高速 INSERT 資料

2019-07-18 09:02 PM

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 囉~

Comments

# 2019-07-25 10:17 AM by Shih Hung

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

# 2019-07-25 08:31 PM by Jeffrey

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

# 2019-10-09 01:04 PM by Shih Hung

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

# 2021-03-08 11:43 AM 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組件就完全沒有問題。

Comments

# 2019-07-25 10:17 AM by Shih Hung

# 2019-07-25 08:31 PM by Jeffrey

# 2019-10-09 01:04 PM by Shih Hung

# 2021-03-08 11:43 AM by Andy

Post a comment