ArrayBinding|V1.3.1|OceanBase Connector/NET|docs|OceanBase Documentations

ArrayBinding is a batch DML optimization feature provided by OceanBase Connector/NET. The driver completes SQL preparation and batch execution in a single network round trip, improving the performance of batch insert, update, and delete operations.

Prerequisites

To use the ArrayBinding feature, all of the following conditions must be met:

Condition	Description
`UseArrayBinding=true`	Enable ArrayBinding in the connection string.
`OracleCommand.ArrayBindCount > 0`	Set`ArrayBindCount`to the number of rows to be processed in batches. If the value is`0`, ArrayBinding is not enabled.
Current session`AutoCommit=false`	Can be initialized via the connection string or set at runtime.
`UseOraclePrepareExecute=true`	Enables the Prepare Execute feature in Oracle-compatible mode.

Note

ArrayBinding requires the current session to have autocommit=0. Calling only BeginTransaction() is insufficient. If switching at runtime, set conn.AutoCommit = false before the batch and set it back to true after the batch to commit implicit transactions and restore auto-commit. If using an explicit OracleTransaction, first set AutoCommit=false, then start the transaction, and end it with Commit() or Rollback().

Limitations

Currently, ArrayBinding is not supported in PL (stored procedures, functions, anonymous blocks).
ArrayBinding applies only to DML statements (CommandType.Text) such as INSERT, UPDATE, and DELETE.
The parameter direction supports only Input. Parameters of types Output, InputOutput, orReturnValue are not supported.
The Value of a parameter must be a one-dimensional array (except for byte[] and char[]), and its length must match ArrayBindCount.
Elements in the same parameter array must have the same data type. For RAW data type, use byte[][].

Examples

Basic usage

The following example shows how to use ArrayBinding to batch insert data:

using OceanBase;

// Enable ArrayBinding and disable AutoCommit in the connection string.
var connectionString = "Data Source=host:port;User Id=username@tenant_name#cluster_name;Password=password;Database=schema_name;UseArrayBinding=true;AutoCommit=false;";

using var conn = new OracleConnection(connectionString);
conn.Open();

using var cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO test_table(a) VALUES(:a)";

// Set the number of rows to batch
cmd.ArrayBindCount = 3;

// Create a parameter and set an array value
var param = new OracleParameter("a", OracleDbType.Varchar2);
param.Value = new string[] { "value1", "value2", "value3" };
cmd.Parameters.Add(param);

// Perform batch insertion using ArrayBinding
int affectedRows = cmd.ExecuteNonQuery();
Console.WriteLine($"Affected rows: {affectedRows}");

// You can use ArrayBindRowsAffected to get the number of affected rows for each row.
if (cmd.ArrayBindRowsAffected is not null)
{
    foreach (var row in cmd.ArrayBindRowsAffected)
        Console.WriteLine($"Rows affected: {row}");
}

Using explicit transactions

The following example shows how to use ArrayBinding in an explicit transaction:

using OceanBase;

var connectionString = "Data Source=host:port;User Id=username@tenant_name#cluster_name;Password=password;Database=schema_name;UseArrayBinding=true;";

using var conn = new OracleConnection(connectionString);
conn.Open();
conn.AutoCommit = false; // Must be set before BeginTransaction

using var transaction = conn.BeginTransaction();
using var cmd = conn.CreateCommand();
cmd.Transaction = transaction;

cmd.CommandText = "INSERT INTO test_table(a) VALUES(:a)";
cmd.ArrayBindCount = 3;

var param = new OracleParameter("a", OracleDbType.Varchar2);
param.Value = new string[] { "data1", "data2", "data3" };
cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();

// Commit a transaction
transaction.Commit();
conn.AutoCommit = true; // Restore automatic commit.

Batch update

The following example shows how to use ArrayBinding to batch update data:

using OceanBase;

var connectionString = "Data Source=host:port;User Id=username@tenant_name#cluster_name;Password=password;Database=schema_name;UseArrayBinding=true;AutoCommit=false;";

using var conn = new OracleConnection(connectionString);
conn.Open();

using var cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE test_table SET a = :a WHERE id = :id";
cmd.ArrayBindCount = 2;

var paramA = new OracleParameter("a", OracleDbType.Varchar2);
paramA.Value = new string[] { "new_value1", "new_value2" };

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

cmd.Parameters.Add(paramA);
cmd.Parameters.Add(paramId);

int affectedRows = cmd.ExecuteNonQuery();
Console.WriteLine($"Number of rows updated: {affectedRows}");

Performance comparison

The performance improvements of ArrayBinding over individual INSERT statements are mainly reflected in the following aspects:

Reduced network round trips: A single network round trip is sufficient to insert a batch of data, avoiding the N round-trip overhead of executing each statement individually.
Reduced parsing overhead: The SQL statement only needs to be parsed once, and the same execution plan can be shared for multiple executions.
Reduced transaction overhead: Keep AutoCommit=false during the batch operation and commit all changes together after the operation is complete, reducing the number of transaction commits.

When inserting a large amount of data in batches (such as several thousand to tens of thousands of rows), the performance improvement of ArrayBinding is particularly significant.