Accelerate MySQL Performance Via ADO.NET Batch Operations
Batch operations are essential for high-performance database applications, yet many developers still rely on inefficient loop-based approaches. This article explores batch processing techniques for MySQL in ADO.NET, examining why traditional methods fall short and demonstrating how to achieve significant performance gains.
Executing DML statements in a loop (the most straightforward approach) creates severe performance bottlenecks:
- Excessive disk I/O caused by the repeated execution of identical statements
- Increased server overhead as each statement requires individual preparation and execution
- Compounding transaction delays that grow with the dataset size
Optimized batch techniques allow processing multiple data changes in a single execution cycle, either through consolidated DML commands or grouped statement execution. Here we demonstrate how using batch capabilities of ADO.NET can improve throughput in MySQL.
Batch INSERT and UPDATE strategies for MySQL
You can execute batch operations through various methods in ADO.NET. First, there is the conventional DataAdapter approach (see Perform batch operations using DataAdapters). Another option is using dotConnect for MySQL, a powerful data provider that delivers superior performance and provides much better flexibility.
dotConnect for MySQL enables batch operations via:
Let's explore each method with practical examples and performance insights.
Create a MySQL test table
To demonstrate batch data insertion and update with practical examples, we will utilize a popular MySQL test database sakila. After deploying this database, let us create a test table in this database.
Execute the following command to create the batch_test table:
CREATE TABLE batch_test (
Id INT AUTO_INCREMENT PRIMARY KEY,
f_integer INT,
f_varchar VARCHAR(100)
);
Batch INSERT with MySqlDataAdapter
As we mentioned earlier, executing batch operations in ADO.NET using DataAdapter is a conventional and well-documented approach. According to the official guidelines, this process involves the following steps:
- Prepare the data: Add multiple DataRow objects to DataTable, or use a pre-filled DataTable from the DataSet.
- Configure MySqlDataAdapter: Initialize the InsertCommand, UpdateCommand, and DeleteCommand properties to define the translation of the DataTable changes into SQL operations.
- Set the batch size: Specify the number of operations to group using the UpdateBatchSize property.
- Execute the batch: Call the Update() method to apply all pending changes to the database.
Below is a code example to perform a batch INSERT operation using MySqlDataAdapter.
using System.Data;
using Devart.Data.MySql;
namespace BatchMySql
{
class Program
{
static void Main(string[] args)
{
string connectionString = "Host=127.0.0.1;User Id=TestUser;Password=TestPassword;Port=3306;Database=sakila;License key=**********";
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// create DataTable
MySqlDataTable dataTable = new MySqlDataTable();
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 MySqlDataAdapter
MySqlDataAdapter adapter = new MySqlDataAdapter();
// set the INSERT command and parameters
adapter.InsertCommand = new MySqlCommand("INSERT INTO batch_test" +
"(id, f_integer, f_varchar) " +
"VALUES" +
"(:id, :f_integer, :f_varchar)",
connection);
adapter.InsertCommand.Parameters.Add("id", MySqlType.Int, 4, "id");
adapter.InsertCommand.Parameters.Add("f_integer", MySqlType.Int, 4, "f_integer");
adapter.InsertCommand.Parameters.Add("f_varchar", MySqlType.VarChar, 100, "f_varchar");
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
// set the UPDATE command and parameters
adapter.UpdateCommand = new MySqlCommand("UPDATE batch_test " +
"SET " +
"f_integer = :f_integer, f_varchar = :f_varchar " +
"WHERE " +
":id = id", connection);
adapter.UpdateCommand.Parameters.Add("id", MySqlType.Int, 4, "id");
adapter.UpdateCommand.Parameters.Add("f_integer", MySqlType.Int, 4, "f_integer");
adapter.UpdateCommand.Parameters.Add("f_varchar", MySqlType.VarChar, 100, "f_varchar");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
// set the DELETE command and parameter
adapter.DeleteCommand = new MySqlCommand("DELETE FROM batch_test " +
"WHERE " +
":id = id", connection);
adapter.DeleteCommand.Parameters.Add("id", MySqlType.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);
}
}
}
}
Batch INSERT with MySqlDataTable
Executing batch operations using MySqlDataAdapter involves multiple steps, thus requiring time and effort. dotConnect for MySQL provides a more straightforward alternative by using the MySqlDataTable class and thus optimizing the process to the following steps:
- Load initial data: Assign SelectCommand to MySqlDataTable and call Open() to populate it.
- Modify data: Perform insert, update, or delete operations directly on MySqlDataTable.
- Set the batch size: Define how many operations to group using the UpdateBatchSize property.
- Execute the batch: Call Update() to apply all pending changes to the database.
Refer to the following code example that demonstrates the described approach.
using Devart.Data.MySql;
namespace BatchMySql
{
class Program
{
static void Main(string[] args)
{
string connectionString = "Host=127.0.0.1;User Id=TestUser;Password=TestPassword;Port=3306;Database=sakila;License key=**********";
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// create MySqlDataTable
MySqlDataTable dataTable = new MySqlDataTable();
dataTable.SelectCommand = new MySqlCommand("SELECT * FROM batch_test", connection);
dataTable.Open();
// add rows to MySqlDataTable
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);
}
}
}
}
This method simplifies the process, as it generates INSERT, UPDATE, and DELETE statements automatically and eliminates the need for manual DataAdapter creation and initialization. You can still override these with custom InsertCommand, UpdateCommand, and DeleteCommand properties if your scenario requires it.
Batch INSERT with MySqlCommand
For executing a single operation type (INSERT, UPDATE, or DELETE), we recommend batch operations with MySqlCommand; it is the highest-performing method available in dotConnect for MySQL. Support for batch operations with MySqlCommand was introduced in version dotConnect 2025.1.
To execute batch operations with MySqlCommand, do the following:
- Set the DML statement: Define the INSERT, UPDATE, or DELETE statement in the CommandText property.
- Bind arrays to parameters: Assign an array of values to each parameter, where each element represents a value for a different execution.
- Execute the batch: Call ExecuteArray() to execute the command for all parameter values in a single batch.
The example below illustrates executing batch operations with MySqlCommand.
using Devart.Data.MySql;
namespace BatchMySql
{
class Program
{
static void Main(string[] args)
{
string connectionString = "Host=127.0.0.1;User Id=TestUser;Password=TestPassword;Port=3306;Database=sakila;License key=**********";
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
// create MySqlCommand
MySqlCommand command = new MySqlCommand("INSERT INTO batch_test" +
"(id, f_integer, f_varchar) " +
"VALUES" +
"(:id, :f_integer, :f_varchar)",
connection);
command.Parameters.Add("id", MySqlType.Int, 4, "id");
command.Parameters.Add("f_integer", MySqlType.Int, 4, "f_integer");
command.Parameters.Add("f_varchar", MySqlType.VarChar, 100, "f_varchar");
// fill MySqlCommand 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);
}
}
}
}
Performance comparison
To help you select the most efficient approach, we benchmarked each method described in this article. The tests measured execution time for INSERT, UPDATE, and DELETE operations on 1,000 records with a batch size of 1,000. Note that results may vary based on your environment and specific use case.
Let us examine the performance of each applied method as illustrated in the graph.
- Simple method: Suggests individual DML operations executed in a loop without transactions. As you can see, this method is not as efficient as other methods, and this baseline demonstrates why you should avoid loop-based approaches.
- In transaction method: Suggests individual DML operations in a loop, but wrapped in a transaction. This method shows significant performance improvement over the simple approach.
- DataAdapter.Update(): The standard ADO.NET approach for batch operations. It is more efficient than individual operations, as it handles multiple DML statements in a single process and supports mixed operation types.
- MySqlDataTable.Update(): Offers better performance than individual operations but consumes more memory, which can impact performance with large datasets.
- MySqlCommand.ExecuteArray(): The fastest method for single-operation-type batches (INSERT, UPDATE, or DELETE only). However, it cannot handle mixed operation types in the same batch.
How to choose the right method
The choice of the appropriate method depends primarily on the operation type.
For single-operation-type batches (INSERT, UPDATE, or DELETE), you should apply MySqlCommand.ExecuteArray(), as it delivers the best performance.
For mixed operation types together, the best option is DataAdapter.Update(), despite lower performance for single-type operations.
Not yet acquainted with dotConnect for MySQL?
dotConnect for MySQL combines optimized batch operations with powerful MySQL-specific features, including multiple connection modes and protocols, advanced data type handling, and comprehensive script management. It enables you to build robust, data-driven applications that use MySQL or MariaDB as part of their backend without worrying about connectivity, while working with familiar ADO.NET technologies.
Start your 30-day trial today and see how dotConnect transforms your MySQL development workflow!