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 |
SetArrayBindCountto the number of rows to be processed in batches. If the value is0, ArrayBinding is not enabled. |
Current sessionAutoCommit=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
Valueof a parameter must be a one-dimensional array (except forbyte[]andchar[]), and its length must matchArrayBindCount. - 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=falseduring 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.