Benchmarking MySqlConnector DbBatch against MariaDB

!results.md

MySQL 8.0.17

Method	Mean	Error	StdDev
Command	403.4 µs	7.959 µs	19.069 µs
Commands	775.9 µs	15.357 µs	28.465 µs
PreparedCommand	359.8 µs	4.708 µs	4.174 µs
PreparedCommands	726.8 µs	14.508 µs	14.899 µs
BatchCommand	390.2 µs	7.572 µs	7.776 µs
BatchCommands	390.5 µs	7.548 µs	10.077 µs
PreparedBatchCommand	399.6 µs	8.906 µs	17.371 µs
PreparedBatchCommands	748.1 µs	14.832 µs	13.874 µs

MariaDB 10.4.7

Method	Mean	Error	StdDev
Command	386.7 µs	7.602 µs	15.529 µs
Commands	757.5 µs	15.311 µs	31.620 µs
PreparedCommand	366.4 µs	7.318 µs	7.830 µs
PreparedCommands	729.8 µs	14.174 µs	18.430 µs
BatchCommand	380.3 µs	8.360 µs	11.989 µs
BatchCommands	380.7 µs	7.590 µs	12.256 µs
PreparedBatchCommand	381.2 µs	7.534 µs	9.529 µs
PreparedBatchCommands	382.7 µs	7.567 µs	14.937 µs

Legend

• Command: calling MySqlCommand.ExecuteReader once with a concatenated SQL statement (current poor man's batching)
• Commands: using MySqlCommand twice, which has noticeable I/O penalty
• PreparedCommands is the same speed as "Commands" because it has to execute statements individually (MySQL limitation)
• (PreparedCommand is just to see if executing a single prepared command provides any speed benefit.)
• BatchCommand is the new MySqlBatch with one command holding concatenated SQL
• BatchCommands uses two separate commands; the implementation sends them in one batch; there's a clear win over "Commands"
• PreparedBatchCommand is a single prepared statement, sent as a "batch" (baseline for comparison)
• PreparedBatchCommands executes two prepared statements in one batch; for MySQL these still have to be sent individually, but for MariaDB they can be combined in one networkpacket

Benchmark.cs

public class Batch
{
	MySqlConnection[] connection;
	MySqlCommand[] preparedCommand;
	MySqlCommand[] preparedCommands;
	MySqlBatch[] preparedBatchCommand;
	MySqlBatch[] preparedBatchCommands;

	[Params(3380, 3104)]
	public int Port { get; set; }

	public Batch()
	{
		connection = new MySqlConnection[2];
		preparedCommand = new MySqlCommand[2];
		preparedCommands = new MySqlCommand[2];
		preparedBatchCommand = new MySqlBatch[2];
		preparedBatchCommands = new MySqlBatch[2];
	}

	[GlobalSetup]
	public void GlobalSetup()
	{ 
		for (var i = 0; i < 2; i++)
		{
			var csb = new MySqlConnectionStringBuilder
			{
				Server = "127.0.0.1",
				Port = (i == 0 ? 3104u : 3380u),
				UserID = "root",
				Password = "test",
				// Database = "test",
				SslMode = MySqlSslMode.None,
				IgnorePrepare = false,
				AllowPublicKeyRetrieval = true,
			};
			connection[i] = new MySqlConnection(csb.ConnectionString);
			connection[i].Open();
			preparedCommand[i] = new MySqlCommand("SELECT  3;", connection[i]);
			preparedCommand[i].Prepare();
			preparedCommands[i] = new MySqlCommand("SELECT  1; SELECT  2;", connection[i]);
			preparedCommands[i].Prepare();
			preparedBatchCommand[i] = new MySqlBatch(connection[i])
			{
				BatchCommands =
				{
					new MySqlBatchCommand("SELECT 1; SELECT  2;"),
				},
			};
			preparedBatchCommand[i].Prepare();
			preparedBatchCommands[i] = new MySqlBatch(connection[i])
			{
				BatchCommands =
				{
					new MySqlBatchCommand("SELECT  1;"),
					new MySqlBatchCommand("SELECT  2;"),
				},
			};
			preparedBatchCommands[i].Prepare();
		}
	}

	[Benchmark]
	public void Command()
	{
		var total = 0;
		using (var command = new MySqlCommand("SELECT 1; SELECT 2;", connection[Port == 3104 ? 0 : 1]))
		{
			using (var reader = command.ExecuteReader())
			{
				do
				{
					while (reader.Read())
					{
						total += reader.GetInt32(0);
					}
				} while (reader.NextResult());
			}
		}
		if (total != 3)
			throw new InvalidOperationException();
	}

	[Benchmark]
	public void Commands()
	{
		var total = 0;
		using (var command = new MySqlCommand("SELECT 1;", connection[Port == 3104 ? 0 : 1]))
		using (var reader = command.ExecuteReader())
		{
			do
			{
				while (reader.Read())
				{
					total += reader.GetInt32(0);
				}
			} while (reader.NextResult());
		}
		using (var command = new MySqlCommand("SELECT 2;", connection[Port == 3104 ? 0 : 1]))
		using (var reader = command.ExecuteReader())
		{
			do
			{
				while (reader.Read())
				{
					total += reader.GetInt32(0);
				}
			} while (reader.NextResult());
		}
		if (total != 3)
			throw new InvalidOperationException();
	}

	[Benchmark]
	public void PreparedCommand()
	{
		var total = 0;
		using (var reader = preparedCommand[Port == 3104 ? 0 : 1].ExecuteReader())
		{
			do
			{
				while (reader.Read())
				{
					total += reader.GetInt32(0);
				}
			} while (reader.NextResult());
		}
		if (total != 3)
			throw new InvalidOperationException();
	}

	[Benchmark]
	public void PreparedCommands()
	{
		var total = 0;
		using (var reader = preparedCommands[Port == 3104 ? 0 : 1].ExecuteReader())
		{
			do
			{
				while (reader.Read())
				{
					total += reader.GetInt32(0);
				}
			} while (reader.NextResult());
		}
		if (total != 3)
			throw new InvalidOperationException();
	}

	[Benchmark]
	public void BatchCommand()
	{
		var total = 0;
		using (var batch = new MySqlBatch(connection[Port == 3104 ? 0 : 1])
		{
			BatchCommands =
			{
				new MySqlBatchCommand("SELECT 1; SELECT 2;"),
			},
		})
		{
			using (var reader = batch.ExecuteReader())
			{
				do
				{
					while (reader.Read())
					{
						total += reader.GetInt32(0);
					}
				} while (reader.NextResult());
			}
		}
		if (total != 3)
			throw new InvalidOperationException();
	}

	[Benchmark]
	public void BatchCommands()
	{
		int total = 0;
		using (var batch = new MySqlBatch(connection[Port == 3104 ? 0 : 1])
		{
			BatchCommands =
			{
				new MySqlBatchCommand("SELECT 1;"),
				new MySqlBatchCommand("SELECT 2;"),
			},
		})
		{
			using (var reader = batch.ExecuteReader())
			{
				do
				{
					while (reader.Read())
					{
						total += reader.GetInt32(0);
					}
				} while (reader.NextResult());
			}
		}
		if (total != 3)
			throw new InvalidOperationException();
	}

	[Benchmark]
	public void PreparedBatchCommand()
	{
		var total = 0;
		using (var reader = preparedBatchCommand[Port == 3104 ? 0 : 1].ExecuteReader())
		{
			do
			{
				while (reader.Read())
				{
					total += reader.GetInt32(0);
				}
			} while (reader.NextResult());
		}
		if (total != 3)
			throw new InvalidOperationException();
	}

	[Benchmark]
	public void PreparedBatchCommands()
	{
		var total = 0;
		using (var reader = preparedBatchCommands[Port == 3104 ? 0 : 1].ExecuteReader())
		{
			do
			{
				while (reader.Read())
				{
					total += reader.GetInt32(0);
				}
			} while (reader.NextResult());
		}
		if (total != 3)
			throw new InvalidOperationException();
	}
}