Batch operations have become increasingly widespread in modern databases due to their ability to enhance performance. This article explores the most widely adopted method for handling batch operations, outlines its limitations, and introduces alternative techniques for more efficient processing.
Batch data insertion or update in a database is a routine database task. However, the most straightforward approach—executing DML statements in a loop—comes with some drawbacks, including:
To overcome these limitations and speed up batch data insertion or update, we recommend using optimized batch techniques. A batch operation typically processes multiple changes in a single execution cycle—either through a single DML statement or by executing multiple statements together. The optimal method depends on the capabilities and behavior of the specific database system in use.
Our examples use the SQLite test database named batch_db.sqlite, which contains the batch_test table.
SQLite is file-based. If the database file does not exist, it is created automatically when you connect to it for the first time. To create the test table, run the following command from the terminal:
sqlite3 batch_db.sqlite "CREATE TABLE batch_test (id INTEGER PRIMARY KEY, f_integer INTEGER, f_varchar TEXT);"
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 SQLite 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 SQLite, batch operations can be executed using:
SQLiteDataAdapterSQLiteDataTableSQLiteCommandLet'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.InsertCommand, UpdateCommand, and DeleteCommand properties of the SQLiteDataAdapter. These commands define how changes in the DataTable should be translated into SQL operations.UpdateBatchSize property of the SQLiteDataAdapter.DataTable to the database by calling the Update() method on the SQLiteDataAdapter.Below is a code example illustrating how to perform a batch INSERT operation using SQLiteDataAdapter.
using Devart.Data.SQLite;
using System.Data;
namespace BatchSqlite
{
class Program
{
static void Main(string[] args)
{
string connectionString = "DataSource=\\batch_db.sqlite;FailIfMissing=False;License key=**********";
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
// create DataTable
SQLiteDataTable dataTable = new SQLiteDataTable();
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 SQLiteDataAdapter
SQLiteDataAdapter adapter = new SQLiteDataAdapter();
// set the INSERT command and parameters
adapter.InsertCommand = new SQLiteCommand("INSERT INTO batch_test" +
"(id, f_integer, f_varchar) " +
"VALUES" +
"(:id, :f_integer, :f_varchar)", connection);
adapter.InsertCommand.Parameters.Add("id", SQLiteType.Int32, 4, "id");
adapter.InsertCommand.Parameters.Add("f_integer", SQLiteType.Int32, 4, "f_integer");
adapter.InsertCommand.Parameters.Add("f_varchar", SQLiteType.Text, 100, "f_varchar");
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
// set the UPDATE command and parameters
adapter.UpdateCommand = new SQLiteCommand("UPDATE batch_test " +
"SET " +
"f_integer = :f_integer, f_varchar = :f_varchar " +
"WHERE " +
":id = id", connection);
adapter.UpdateCommand.Parameters.Add("f_integer", SQLiteType.Int32, 4, "f_integer");
adapter.UpdateCommand.Parameters.Add("f_varchar", SQLiteType.Text, 100, "f_varchar");
adapter.UpdateCommand.Parameters.Add("id", SQLiteType.Int32, 4, "id");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
// set the DELETE command and parameter
adapter.DeleteCommand = new SQLiteCommand("DELETE FROM batch_test " +
"WHERE " +
":id = id", connection);
adapter.DeleteCommand.Parameters.Add("id", SQLiteType.Int32, 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 SQLiteDataAdapter involves multiple effort- and time-consuming steps such as creating DataAdapter, initializing and defining its parameters and so on.
dotConnect for SQLite provides a straightforward alternative way to execute batch operations. This approach uses the properties and methods of the SQLiteDataTable class. Therefore, it allows streamlining the process significantly with these simple steps:
SelectCommand to the SQLiteDataTable, then call the Open() method to populate it with data.SQLiteDataTable.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.SQLite;
namespace BatchSqlite
{
class Program
{
static void Main(string[] args)
{
string connectionString = "DataSource=\\batch_db.sqlite;FailIfMissing=False;License key=**********";
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
// create SQLiteDataTable
SQLiteDataTable dataTable = new SQLiteDataTable();
dataTable.SelectCommand = new SQLiteCommand("SELECT * FROM batch_test", connection);
dataTable.Open();
// add rows to SQLiteDataTable
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 SQLite — batch operations with SQLiteCommand. Spoiler: this method stands out among the rest in terms of performance.
To execute batch operations with SQLiteCommand, follow these steps:
INSERT, UPDATE, or DELETE statement in the CommandText property.SQLiteCommand. 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 SQLiteCommand.
using Devart.Data.SQLite;
namespace BatchSqlite
{
class Program
{
static void Main(string[] args)
{
string connectionString = "DataSource=\\batch_db.sqlite;FailIfMissing=False;License key=**********";
try
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
// create SQLiteCommand
SQLiteCommand command = new SQLiteCommand("INSERT INTO batch_test" +
"(id, f_integer, f_varchar) " +
"VALUES" +
"(:id, :f_integer, :f_varchar)", connection);
command.Parameters.Add("id", SQLiteType.Int32, 4, "id");
command.Parameters.Add("f_integer", SQLiteType.Int32, 4, "f_integer");
command.Parameters.Add("f_varchar", SQLiteType.Text, 100, "f_varchar");
// fill SQLiteCommand 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 SQLiteDataTable, batch operations using SQLiteCommand 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. This method is more efficient than executing individual operations, as it can handle multiple DML operations in a single process.SQLiteDataTable object to update records in bulk. Although it performs better than simple operations, its main drawback is higher memory usage, which can negatively impact performance when working with large datasets.INSERT, UPDATE, or DELETE statements. 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—SQLiteCommand.ExecuteArray() is the most time-efficient method. However, for 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 SQLite 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 SQLite a try and explore its powerful capabilities with our 30-day trial. Don't miss out on experiencing the full potential of the SQLite applications!