Improve Db2 Performance With ADO.NET Batch Processing

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:

• Increased disk I/O caused by executing the same statement multiple times.
• Greater use of server resources, as each statement must be individually prepared and executed.
• Potential response delays due to time-consuming transaction processing.

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.

Before getting started

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

Batch insert and update strategies for Db2

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:

• DB2DataAdapter
• DB2DataTable
• DB2Command

Let’s explore each method with practical examples and performance insights.

Batch INSERT using DB2DataAdapter

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:

1. Prepare the data: Add multiple 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.
2. Configure the DB2DataAdapter: Initialize the InsertCommand, UpdateCommand, and DeleteCommand properties. These commands define how changes in the DataTable should be translated into SQL operations.
3. Set the batch size: Specify the number of operations to group in a single batch by setting the UpdateBatchSize property.
4. Execute the batch: Call the 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);
            }
        }
    }
}

Batch INSERT using DB2DataTable

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:

1. Load initial data: Assign a SelectCommand to the DB2DataTable, then call the Open() method to populate it with data.
2. Modify data in DB2DataTable: Perform insert, update, or delete operations directly on the DB2DataTable.
3. Set the batch size: Define how many operations should be grouped together by setting the UpdateBatchSize property.
4. Execute the batch: Call the 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);
            }
        }
    }
}

Batch INSERT using DB2Command

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:

1. Set the DML statement: Define the desired INSERT, UPDATE, or DELETE statement in the CommandText property.
2. Bind arrays to parameters: Assign an array of values to each parameter of DB2Command. Each array element represents a value for a different execution of the statement.
3. Execute the batch: Call the 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.

Performance comparison

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.

• Simple method: The first chart bar presents the results after executing individual DML operations in a loop. Please note that we intentionally used this approach to demonstrate the poor performance of operations executed in a loop.
• In transaction method: Shows the results after executing individual DML operations in a loop, but within the context of a transaction. This method significantly enhances performance.
• DataAdapter.Update(): Illustrates the performance of the ADO.NET DataAdapter to batch-update records.
• DB2DataTable.Update(): Represents the performance while using the DB2DataTable object to update records in bulk. Although it performs better than simple operations, its main drawback is higher memory usage.
• DB2Command.ExecuteArray(): Proved to be the fastest method for processing batch operations of a single type. However, it does not support executing mixed operation types within the same batch.

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 yet acquainted with dotConnect for Db2?

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!