Ultra-High Performance Bulk Processing (Array Binding, Benchmarking & Optimization)

100

Article

Introduction

In Part 1, we implemented bulk CRUD operations using:

Global Temporary Tables (GTT)
Stored Procedures
Dapper-based bulk inserts

However, when dealing with 100K–1M+ records, even that approach can be further optimized.

In this article, we will explore:

Oracle Array Binding (ODP.NET)
Performance Benchmarking
Parallel Batch Processing
Memory & Transaction Optimization

Why Go Beyond Dapper?

While Dapper is fast, it still executes:

⚠️ Multiple parameterized executions internally

Problem:

Still incurs network round-trips
Not truly “bulk” at Oracle driver level

Solution: Oracle Array Binding (ODP.NET)

What is Array Binding?

Array Binding allows you to:

✔ Send entire arrays of data in one call
✔ Execute bulk insert/update in a single round-trip
✔ Achieve 10x–50x performance improvement

Bulk Insert Using Array Binding

Implementation

using Oracle.ManagedDataAccess.Client;
using System.Data;

public async Task BulkInsert_ArrayBinding(List<Employee> employees)
{
    using var connection = new OracleConnection(_connectionString);
    await connection.OpenAsync();

    using var transaction = connection.BeginTransaction();

    using var command = connection.CreateCommand();
    command.Transaction = transaction;

    command.CommandText = @"
        INSERT INTO EMPLOYEES (ID, NAME, EMAIL, SALARY)
        VALUES (EMP_SEQ.NEXTVAL, :Name, :Email, :Salary)";

    // Array Binding
    command.ArrayBindCount = employees.Count;

    command.Parameters.Add(":Name", OracleDbType.Varchar2,
        employees.Select(e => e.Name).ToArray(), ParameterDirection.Input);

    command.Parameters.Add(":Email", OracleDbType.Varchar2,
        employees.Select(e => e.Email).ToArray(), ParameterDirection.Input);

    command.Parameters.Add(":Salary", OracleDbType.Decimal,
        employees.Select(e => e.Salary).ToArray(), ParameterDirection.Input);

    await command.ExecuteNonQueryAsync();

    transaction.Commit();
}

Bulk Update Using Array Binding

public async Task BulkUpdate_ArrayBinding(List<Employee> employees)
{
    using var connection = new OracleConnection(_connectionString);
    await connection.OpenAsync();

    using var transaction = connection.BeginTransaction();

    using var command = connection.CreateCommand();
    command.Transaction = transaction;

    command.CommandText = @"
        UPDATE EMPLOYEES 
        SET NAME = :Name, EMAIL = :Email, SALARY = :Salary
        WHERE ID = :Id";

    command.ArrayBindCount = employees.Count;

    command.Parameters.Add(":Name", OracleDbType.Varchar2,
        employees.Select(e => e.Name).ToArray(), ParameterDirection.Input);

    command.Parameters.Add(":Email", OracleDbType.Varchar2,
        employees.Select(e => e.Email).ToArray(), ParameterDirection.Input);

    command.Parameters.Add(":Salary", OracleDbType.Decimal,
        employees.Select(e => e.Salary).ToArray(), ParameterDirection.Input);

    command.Parameters.Add(":Id", OracleDbType.Int32,
        employees.Select(e => e.Id).ToArray(), ParameterDirection.Input);

    await command.ExecuteNonQueryAsync();

    transaction.Commit();
}

Bulk Delete Using Array Binding

public async Task BulkDelete_ArrayBinding(List<int> ids)
{
    using var connection = new OracleConnection(_connectionString);
    await connection.OpenAsync();

    using var transaction = connection.BeginTransaction();

    using var command = connection.CreateCommand();
    command.Transaction = transaction;

    command.CommandText = @"DELETE FROM EMPLOYEES WHERE ID = :Id";

    command.ArrayBindCount = ids.Count;

    command.Parameters.Add(":Id", OracleDbType.Int32,
        ids.ToArray(), ParameterDirection.Input);

    await command.ExecuteNonQueryAsync();

    transaction.Commit();
}

Performance Benchmark Comparison

Approach	100K Records Time	DB Calls	Performance
Row-by-Row	30–60 sec	100K	Very Slow
Dapper Bulk	8–15 sec	Multiple	Medium
GTT + SP	3–6 sec	Few	Fast
Array Binding	1–2 sec	1	Ultra Fast

Parallel Batch Processing (Advanced)

For extremely large datasets (500K+), combine:

✔ Array Binding
✔ Parallel Processing

var batches = employees.Chunk(20000);

await Task.WhenAll(batches.Select(batch =>
    BulkInsert_ArrayBinding(batch.ToList())));

Memory Optimization Techniques

1. Stream Data Instead of Loading All

Avoid:

var data = GetAllRecords(); //  Loads everything in memory

Use:

await foreach (var batch in GetBatchesAsync())
{
    await BulkInsert_ArrayBinding(batch);
}

2. Control Batch Size

Batch Size	Use Case
5K–10K	Safe default
20K–50K	High performance
100K+	Risky (memory spike)

Transaction Optimization

Best Practices

Keep transactions short-lived
Use one transaction per batch
Avoid long locks

Error Handling Strategy

try
{
    await BulkInsert_ArrayBinding(data);
}
catch (OracleException ex)
{
    // Log error
    throw;
}

When to Use What?

Scenario	Best Approach
<10K records	Dapper
10K–100K	GTT + Stored Procedure
100K–1M+	🚀 Array Binding
Real-time streaming	Batch + Array Binding

Key Takeaways

Array Binding is the fastest way to handle bulk operations in Oracle
Reduces network overhead to near zero
Perfect for enterprise-scale applications
Can be combined with batching and parallelism

Conclusion

By leveraging Oracle array binding, you unlock maximum database performance in ASP.NET Core applications. Combined with proper batching and transaction control, this approach can scale to millions of records efficiently.