Oracle bulk insert running slow with dapper-CodePudding

When we performed bulk insertion with Oracle as specified in the document, it transferred 200 data in 10 seconds. How can I do this more efficiently?

public async Task InsertAsync()
{
    var items = GetItems();
    using var connection = OpenOracleConnection();
    var transaction = connection.BeginTransaction();
    await connection.ExecuteAsync(@"insert into TableName
    (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8 ,Column9, Column10) VALUES 
    (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10) ", items, transaction: transaction);

    transaction.Commit();
}


private static IEnumerable<object> GetItems()
{
    var list = new List<object>();
    for (int i = 0; i < 200; i  )
    {
        list.Add(new
        {
            p1 = new Random().Next(0,999999),
            p2 = 1,
            p3 = "-",
            p4 = "--",
            p5 = "--",
            p6 = "---",
            p7 = "----",
            p8 = DateTime.Now,
            p9 = "-",
            p10 = 1
        });
    }
    return list;
}

CodePudding user response：

Your current code is not really doing a Bulk Insertion. It insert once by once every item in your list

Learn about Array Binding insertion if you really want to do a BulkInsert in Oracle with the fastest performance.

Disclaimer: I'm the owner of the project Dapper Plus

This project is not free but lets you easily do bulk insert in Oracle (use also Array Binding under the hood).

// Your mapping
DapperPlusManager.Entity<TEntity>().ToTable("TableName")

// ...code...

transaction.BulkInsert(items)