Batch operations are widely used in modern databases for their performance benefits. This article examines the most commonly adopted approach to batch processing, discusses its drawbacks, and presents alternative methods for achieving greater efficiency.
Performing batch inserts or updates is a common practice for handling database modifications. However, using the most straightforward approach—running DML statements in a loop—can lead to some disadvantages, including:
To address these limitations and ensure the batch data inserts or updates are efficient, we recommend using optimized batch techniques. Batch operations generally handle multiple data changes in one execution, either via a single DML command or by running several statements together. The choice of the most effective approach will vary based on the capabilities and behavior of the target database system.
In this article, we will demonstrate batch data insertion and update with practical examples. Our examples use the Db2 test database, which contains the batch_test table.
With any SQL tool, you can create the test database and its table by using the following DDL statement:
CREATE TABLE BATCH_TEST (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
F_INTEGER INTEGER,
F_VARCHAR VARCHAR(100),
PRIMARY KEY (ID)
);
There are various ways to execute batch operations. While the conventional one in ADO.NET involves using DataAdapter (see Perform batch operations using DataAdapters), dotConnect for Db2 offers several alternative approaches, allowing users to select the one that best fits their needs. Additionally, they demonstrate better performance in certain scenarios.
In dotConnect for Db2, batch operations can be executed using:
DB2DataAdapterDB2DataTableDB2CommandLet’s explore each method with practical examples and performance insights.
As mentioned earlier, executing batch operations in ADO.NET using DataAdapter is regarded as a conventional and well-documented approach. According to the official guidelines, the process involves the following steps:
DataRow objects to the DataTable that holds the data you want to insert into the database. You can also use a pre-filled DataTable created via a DataSet. This is useful when you’re working with an existing database table and need to insert, update, or delete specific records in bulk.InsertCommand, UpdateCommand, and DeleteCommand properties. These commands define how changes in the DataTable should be translated into SQL operations.UpdateBatchSize property.Update() method on the DB2DataAdapter.Below is a code example illustrating how to perform the batch INSERT operation using DB2DataAdapter.
using Devart.Data.DB2;
using System.Data;
namespace BatchDb
{
class Program
{
static void Main(string[] args)
{
string connectionString = "Server=127.0.0.1:50000;Database=TestDb;uid=TestUser;pwd=TestPassword;currentSchema=test;License key=**********";
try
{
using (DB2Connection connection = new DB2Connection(connectionString))
{
connection.Open();
// create DataTable
DB2DataTable dataTable = new DB2DataTable();
dataTable.Columns.Add("id", typeof(int));
dataTable.Columns.Add("f_integer", typeof(int));
dataTable.Columns.Add("f_varchar", typeof(string));
// fill DataTable with data
dataTable.Rows.Add([1, 1, "string 1"]);
dataTable.Rows.Add([2, 2, "string 2"]);
dataTable.Rows.Add([3, 3, "string 3"]);
dataTable.Rows.Add([4, 4, "string 4"]);
dataTable.Rows.Add([5, 5, "string 5"]);
// create DB2DataAdapter
DB2DataAdapter adapter = new DB2DataAdapter();
// set the INSERT command and parameters
adapter.InsertCommand = new DB2Command("INSERT INTO batch_test" +
"(id, f_integer, f_varchar) " +
"VALUES" +
"(:id, :f_integer, :f_varchar)", connection);
adapter.InsertCommand.Parameters.Add("id", DB2Type.Integer, 4, "id");
adapter.InsertCommand.Parameters.Add("f_integer", DB2Type.Integer, 4, "f_integer");
adapter.InsertCommand.Parameters.Add("f_varchar", DB2Type.VarChar, 100, "f_varchar");
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
// set the UPDATE command and parameters
adapter.UpdateCommand = new DB2Command("UPDATE batch_test " +
"SET " +
"f_integer = :f_integer, f_varchar = :f_varchar " +
"WHERE " +
":id = id", connection);
adapter.UpdateCommand.Parameters.Add("id", DB2Type.Integer, 4, "id");
adapter.UpdateCommand.Parameters.Add("f_integer", DB2Type.Integer, 4, "f_integer");
adapter.UpdateCommand.Parameters.Add("f_varchar", DB2Type.VarChar, 100, "f_varchar");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
// set the DELETE command and parameter
adapter.DeleteCommand = new DB2Command("DELETE FROM batch_test " +
"WHERE " +
":id = id", connection);
adapter.DeleteCommand.Parameters.Add("id", DB2Type.Integer, 4, "id");
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
// set the batch size
adapter.UpdateBatchSize = 5;
// execute the update
adapter.Update(dataTable);
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
}
As previously mentioned, executing batch operations using DB2DataAdapter involves multiple effort- and time-consuming steps such as creating DataAdapter, initializing and defining its parameters, and so on.
dotConnect for Db2 provides a straightforward alternative way to execute batch operations. This approach uses the properties and methods of the DB2DataTable class. Therefore, it allows streamlining the process significantly with these simple steps:
SelectCommand to the DB2DataTable, then call the Open() method to populate it with data.DB2DataTable.UpdateBatchSize property.Update() method to apply all pending changes to the database in a single batch.Refer to the following code example that demonstrates the described approach.
using Devart.Data.DB2;
namespace BatchDb
{
class Program
{
static void Main(string[] args)
{
string connectionString = "Server=127.0.0.1:50000;Database=TestDb;uid=TestUser;pwd=TestPassword;currentSchema=test;License key=**********";
try
{
using (DB2Connection connection = new DB2Connection(connectionString))
{
connection.Open();
// create DB2DataTable
DB2DataTable dataTable = new DB2DataTable();
dataTable.SelectCommand = new DB2Command("SELECT * FROM batch_test",
connection);
dataTable.Open();
// add rows to DB2DataTable
dataTable.Rows.Add([1, 1, "string 1"]);
dataTable.Rows.Add([2, 2, "string 2"]);
dataTable.Rows.Add([3, 3, "string 3"]);
dataTable.Rows.Add([4, 4, "string 4"]);
dataTable.Rows.Add([5, 5, "string 5"]);
// set the batch size
dataTable.UpdateBatchSize = 5;
// execute the update
dataTable.Update();
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
}
The example shows that this method does not require users to manually create and initialize DataAdapter. The necessary statements, such as INSERT, UPDATE, and DELETE, will be automatically generated. However, if needed, you can explicitly initialize the InsertCommand, UpdateCommand, and DeleteCommand properties, just like you would do for DataAdapter.
If you need to execute a single type of batch operation, such as INSERT, UPDATE, or DELETE, we recommend using an alternative method provided by dotConnect for Db2—batch operations with DB2Command. Spoiler: this method stands out among the rest in terms of performance.
To execute batch operations with DB2Command, follow these steps:
INSERT, UPDATE, or DELETE statement in the CommandText property.DB2Command. Each array element represents a value for a different execution of the statement.ExecuteArray() method to execute the command for all sets of parameter values in a single batch.The example below illustrates the execution of batch operations using DB2Command.
using Devart.Data.DB2;
namespace BatchDb
{
class Program
{
static void Main(string[] args)
{
string connectionString = "Server=127.0.0.1:50000;Database=TestDb;uid=TestUser;pwd=TestPassword;currentSchema=test;License key=**********";
try
{
using (DB2Connection connection = new DB2Connection(connectionString))
{
connection.Open();
// create DB2Command
DB2Command command = new DB2Command("INSERT INTO batch_test" +
"(id, f_integer, f_varchar) " +
"VALUES" +
"(:id, :f_integer, :f_varchar)", connection);
command.Parameters.Add("id", DB2Type.Integer, 4, "id");
command.Parameters.Add("f_integer", DB2Type.Integer, 4, "f_integer");
command.Parameters.Add("f_varchar", DB2Type.VarChar, 100, "f_varchar");
// fill DB2Command parameter values
command.Parameters["id"].Value = new int[5] { 1, 2, 3, 4, 5 };
command.Parameters["f_integer"].Value = new int[5] { 1, 2, 3, 4, 5 };
command.Parameters["f_varchar"].Value = new string[5] { "string 1", "string 2", "string 3", "string 4", "string 5" };
// execute the update
command.ExecuteArray();
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
}
It is worth mentioning that, unlike DataAdapter and DB2DataTable, batch operations using DB2Command do not allow explicitly setting the batch size, as there is no property equivalent to UpdateBatchSize. Instead, the optimal batch size is automatically calculated.
To sum up, we present a comparative chart showcasing the time spent executing batch operations using each of the methods described in this article. This chart highlights the performance differences, helping you choose the most efficient method for your needs. Please note, however, that these results are not absolute and may vary depending on the test environment and specific use case.
As part of the research, we calculated the time spent executing the INSERT, UPDATE, and DELETE commands within the range of 1000 records. The batch size is 1000.
Refer to the chart below for performance results of batch operations executed using the methods outlined in this article.
Let us now review the results represented in the chart.
DataAdapter to batch-update records.DB2DataTable object to update records in bulk. Although it performs better than simple operations, its main drawback is higher memory usage.To sum up, the results presented in the chart confirm our insights. If you need to execute a specific type of batch operation—such as INSERT, UPDATE, or DELETE—Command.ExecuteArray() is the most time-efficient method. However, for the scenarios where different types of operations must be executed together, DataAdapter.Update() becomes a more practical choice despite its lower performance in single-type batch processing.
Not only does our state-of-the-art dotConnect for Db2 offer optimized components for bulk operations, but it also supports a wide range of database-specific features, including multiple connection modes and protocols, advanced data type handling, and robust script management.
We invite you to give dotConnect for Db2 a try and explore its powerful capabilities with our 30-day trial. Don’t miss out on experiencing the full potential of Db2 applications!