c# - 如何在 Oracle DB 中使用 DataTable(或类似的)

Question

我最近一直在处理一些性能问题，并试图弄清楚如何以指数方式提高对 Oracle DB 的某些调用的性能。

技术:

.NET 核心 2

Oracle 数据库

小巧玲珑(可选)

Devart (可选)

OracleManaged (可选，测试版)

我熟悉 DataTable的使用Dapper 和 SQL Server 的表值参数，并希望使用上述技术进行复制。我尚未能够重现以下解决方案以与 Devart 或 OracleManaged 一起使用:

The code below is not what I'm running... It's a paraphrased example. I just need something that works with Oracle to pass a DataTable or array of objects to be used in a query/insert.

SQL 服务器:

CREATE TYPE MyCustomerInfo AS TABLE
(
    Id BIGINT NOT NULL,
    --Name NVARCHAR(32) NOT NULL,
    --...
);

用于 SQL Server 的 C#:

const string getCustomersSql = @"
    SELECT
        c.Id,
        --c.Name
        --...
    FROM @myCustomers mc
    LEFT JOIN Customers c
        ON c.Id = mc.Id";

var myCustomers = new DataTable();
myCustomers.Columns.Add("Id", typeof(long));
//...

myCustomers.Rows.Add(1);
myCustomers.Rows.Add(2);

var customers = await sqlDbConnection.QueryAsync<Customer>(getCustomersSql, new { myCustomers = myCustomers.AsTableValuedParameter("MyCustomerInfo") });

Oracle 数据库 (PL/SQL):

CREATE TYPE MY_CUSTOMER_INFO AS OBJECT
(
    ID BIGINT,
    --NAME VARCHAR2(32),
    --...
);

CREATE TYPE MY_CUSTOMER_INFO_ARRAY AS TABLE OF MY_CUSTOMER_INFO;

用于 Oracle 数据库的 C#:

我采用了与 SQL Server 类似的方法，但使用 Devart 和 OracleManaged 都没有奏效。我也 [非常不高兴] 尝试使用 OracleCommand和 OracleParameter直接 - 再次使用 Devart 和 OracleManaged - 无济于事。

我与 Devart 的结果似乎表明该功能是有意阻止的。我对 OracleManaged 的​​结果似乎表明它尚未实现，这并不奇怪，因为它是测试版(据说今年第三季度发布)。

我的下一个方法可能是在 Devart 中使用关联数组(我几乎没有经验或学习的意愿)。在这一点上，我只是在探索可以带来类似性能提升的东西......

编辑 :使用每个参数的数组方法是可能的，但非常不方便，因为大型类最终会包含 12 个以上的数组。我想要这种方法的替代方法。

Answer 1

@Kody 这篇文章有点旧，所以这可能对您没有帮助，但对其他人可能会有所帮助。虽然我不知道有什么方法可以将数据表/UDT 与托管 Oracle 客户端一起使用，但如果您只想减少到数据库的往返次数并通过一次调用执行一堆插入/删除/更新您可以尝试这种方法的数据库:

using (var dbConn = ManagedOracleHelper.GetConnection())
{
    dbConn.Open();
    var cmd = dbConn.CreateCommand();
    var udtList = GetUDTList(); // A dummy method to get a collection of Model 
                                // objects you want to use for the bulk operation.
                                // This could be a dataset too, you would just need
                                // to change the code within the for-loop to iterate
                                // over rows and access the columns by name.

    var firstNameArr = new string[udtList.Count];
    var lastNameArr  = new string[udtList.Count];
    var emailArr     = new string[udtList.Count];

    for (var i = 0; i < udtList.Count; i++)
    {
        firstNameArr[i] = udtList[i].FirstName;
        lastNameArr[i]  = udtList[i].LastName;
        emailArr[i]     = udtList[i].Email;
    }

    cmd.CommandText = @"INSERT INTO CUSTOMERS(FIRST_NAME, LAST_NAME, EMAIL)
                        VALUES(:FirstName, :LastName, :Email)";;
    cmd.BindByName  = true;

    cmd.Parameters.Add("FirstName", OracleDbType.Varchar2, ParameterDirection.Input);
    cmd.Parameters.Add("LastName", OracleDbType.Varchar2, ParameterDirection.Input);
    cmd.Parameters.Add("Email", OracleDbType.Varchar2, ParameterDirection.Input);

    cmd.ArrayBindCount = udtList.Count;

    cmd.Parameters["FirstName"].Value = firstNameArr;
    cmd.Parameters["LastName"].Value  = lastNameArr;
    cmd.Parameters["Email"].Value     = emailArr;

    cmd.ExecuteNonQuery();
}

如果您插入/更新数亿条记录，我不确定这会有多高的性能，但我已经通过插入 80K 条记录对此进行了测试，并且我的导入功能从花费几分钟开始(以前有人将其编码为一个简单的循环，调用插入每个记录)下降到几秒钟。我没有确切的数字，因为一旦导入时间减少了近 2 个数量级，我就很高兴继续处理其他事情。