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.
To demonstrate efficient batch data insertion and update, the following examples will use a SQL Server test database. We'll be working with a table named batch_test.
You can create both the database and the table using any standard SQL tool with the following Data Definition Language (DDL) statement:
CREATE TABLE dbo.batch_test (
id INT PRIMARY KEY IDENTITY(1,1),
f_integer INT NULL,
f_varchar VARCHAR(100) NULL
);
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 SQL Server 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 SQL Server, batch operations can be executed using:
SqlDataAdapterSqlDataTableSqlCommandLet'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 of the SqlDataAdapter. These commands define how changes in the DataTable should be translated into SQL operations.
UpdateBatchSize property of the SqlDataAdapter.
DataTable to the database by calling the Update() method on the SqlDataAdapter.
Below is a code example illustrating how to perform a batch INSERT operation using SqlDataAdapter.
using Devart.Data.SqlServer;
using System.Data;
namespace BatchSqlServer
{
class Program
{
static void Main(string[] args)
{
string connectionString = "DataSource=localhost;InitialCatalog=TestDb;IntegratedSecurity=True;TrustServerCertificate=True;Asynchronous Processing=true;License key=**********";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// create DataTable
SqlDataTable dataTable = new SqlDataTable();
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 SqlDataAdapter
SqlDataAdapter adapter = new SqlDataAdapter();
// set the INSERT command and parameters
adapter.InsertCommand = new SqlCommand("INSERT INTO batch_test" +
"([id], [f_integer], [f_varchar]) " +
"VALUES" +
"(@id, @f_integer, @f_varchar)",
connection);
adapter.InsertCommand.Parameters.Add("id", SqlType.Int, 4, "id");
adapter.InsertCommand.Parameters.Add("f_integer", SqlType.Int, 4, "f_integer");
adapter.InsertCommand.Parameters.Add("f_varchar", SqlType.VarChar, 100, "f_varchar");
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
// set the UPDATE command and parameters
adapter.UpdateCommand = new SqlCommand("UPDATE batch_test " +
"SET " +
"f_integer = @f_integer, f_varchar = @f_varchar " +
"WHERE " +
"@id = id", connection);
adapter.UpdateCommand.Parameters.Add("id", SqlType.Int, 4, "id");
adapter.UpdateCommand.Parameters.Add("f_integer", SqlType.Int, 4, "f_integer");
adapter.UpdateCommand.Parameters.Add("f_varchar", SqlType.VarChar, 100, "f_varchar");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
// set the DELETE command and parameter
adapter.DeleteCommand = new SqlCommand("DELETE FROM batch_test " +
"WHERE " +
"@id = id",
connection);
adapter.DeleteCommand.Parameters.Add("id", SqlType.Int, 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 SqlDataAdapter involves multiple effort- and time-consuming steps such as creating DataAdapter, initializing and defining its parameters, and so on.
dotConnect for SQL Server provides a straightforward alternative way to execute batch operations. This approach uses the properties and methods of the SqlDataTable class. Therefore, it allows streamlining the process significantly with these simple steps:
SelectCommand to the SqlDataTable, then call the Open() method to populate it with data.
SqlDataTable.
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.SqlServer;
namespace BatchSqlServer
{
class Program
{
static void Main(string[] args)
{
string connectionString = "DataSource=localhost;InitialCatalog=TestDb;IntegratedSecurity=True;TrustServerCertificate=True;Asynchronous Processing=true;License key=**********";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// create SqlDataTable
SqlDataTable dataTable = new SqlDataTable();
dataTable.SelectCommand = new SqlCommand("SELECT * FROM batch_test", connection);
dataTable.Open();
// add rows to SqlDataTable
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 SQL Server—batch operations with SqlCommand. Spoiler: this method stands out among the rest in terms of performance.
To execute batch operations with SqlCommand, follow these steps:
INSERT, UPDATE, or DELETE statement in the CommandText property.
SqlCommand. 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 SqlCommand.
using Devart.Data.SqlServer;
namespace BatchSqlServer
{
class Program
{
static void Main(string[] args)
{
string connectionString = "DataSource=localhost;InitialCatalog=TestDb;IntegratedSecurity=True;TrustServerCertificate=True;Asynchronous Processing=true;License key=**********";
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// create SqlCommand
SqlCommand command = new SqlCommand("INSERT INTO batch_test" +
"([id], [f_integer], [f_varchar]) " +
"VALUES" +
"(@id, @f_integer, @f_varchar)", connection);
command.Parameters.Add("id", SqlType.Int, 4, "id");
command.Parameters.Add("f_integer", SqlType.Int, 4, "f_integer");
command.Parameters.Add("f_varchar", SqlType.VarChar, 100, "f_varchar");
// fill SqlCommand 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 SqlDataTable, batch operations using SqlCommand 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.SqlDataTable object to update records in bulk.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—SqlCommand.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 SQL Server 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 SQL Server a try and explore its powerful capabilities with our 30-day trial. Don't miss out on experiencing the full potential of the SQL Server applications!