ArrayBinding|V1.2.0|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
Connection parameters`UseArrayBinding=true`	Set in the connection string.`UseArrayBinding=true`to enable the ArrayBinding feature.
`OracleCommand.ArrayBindCount > 0`	Set on the command object.`ArrayBindCount`The number of rows to be processed in batches. The value is`0`This command does not enable ArrayBinding.
Disable AutoCommit or enable explicit transactions.	To be set to`AutoCommit=false`, or enable explicit transactions before performing the ArrayBinding operation (`BeginTransaction`).
Parameter`UseOraclePrepareExecute=true`	To be set to`UseOraclePrepareExecute=true`to enable the Prepare Execute feature in Oracle-compatible mode.

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 Batch Row Count
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 obtain 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();

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();

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: By using explicit transactions or disabling AutoCommit, the number of transaction commits is reduced.

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.