Skip to content

Instantly share code, notes, and snippets.

@bgrainger
Last active August 8, 2019 14:13
Show Gist options
  • Save bgrainger/0504a52065eeade9e4b30e88b6363f2c to your computer and use it in GitHub Desktop.
Save bgrainger/0504a52065eeade9e4b30e88b6363f2c to your computer and use it in GitHub Desktop.
Benchmarking MySqlConnector DbBatch against MariaDB

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
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();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment