MySqlDataAdapter.Update is very slow

[bgrainger]

@bgrainger this issue seems to still be a problem in the latest version of MySqlConnector (re: mrogunlana/Panama#15), hopefully, it's due to a user error on my part, if you're interested in some context of how I implemented the adapter using MySqlConnector, please see: https://github.com/mrogunlana/Panama.Core/blob/40d6e16a2fc2cdb97bbf56023b75ec913aadef9a/Panama.MySql.Dapper/MySqlQuery.cs#L574

I've updated the MySqlDataAdapter since then to use MySql.Data here temporarily here: https://github.com/mrogunlana/Panama.Core/blob/master/Panama.MySql.Dapper/MySqlQuery.cs#L568 it's essentially the same code, but it's out performing MySqlConnector by a very high margin e.g. MySqlConnector currently benchmarks at 50K rows at around 10 minutes whereas MySql.Data runs a full 100k around 1 minute.

Any ideas/guidance here would be much appreciated!

Originally posted by @mrogunlana in #673 (comment)

[bgrainger]

@mrogunlana I adapted the Panama code into the following:

It takes 2.59s for MySqlConnector and 2.57s for MySql.Data 8.0.28 (for 2,000 rows). Any suggestions on what to change to reproduce the bug?

var connectionString = "Server=localhost;User ID=root;Password=xxx;Database=mysqltest;SSL Mode=None;";

using (var connection = new MySqlConnector.MySqlConnection(connectionString))
{
	connection.Open();
	connection.Execute("drop table if exists data_adapter; create table data_adapter(id integer not null primary key, bytes tinyblob, number integer);");
	
	using (var adapter = new MySqlConnector.MySqlDataAdapter("SELECT id, bytes, number from data_adapter", connection))
	using (var insertCommand = new MySqlConnector.MySqlCommand("INSERT INTO data_adapter(id, bytes, number) VALUES(@id, @bytes, @number);", connection))
	{
		insertCommand.UpdatedRowSource = UpdateRowSource.None;
		insertCommand.Parameters.Add(new MySqlConnector.MySqlParameter("@id", MySqlConnector.MySqlDbType.Int32) { SourceColumn = "id" });
		insertCommand.Parameters.Add(new MySqlConnector.MySqlParameter("@bytes", MySqlConnector.MySqlDbType.TinyBlob) { SourceColumn = "bytes" });
		insertCommand.Parameters.Add(new MySqlConnector.MySqlParameter("@number", MySqlConnector.MySqlDbType.Int32) { SourceColumn = "number" });
		
		adapter.InsertCommand = insertCommand;
		adapter.UpdateBatchSize = 100;
		
		var sw = Stopwatch.StartNew();
		adapter.Update(MakeDataTable());
		sw.Stop();
		sw.Elapsed.Dump();
	}
	connection.Query<long>("select count(*) from data_adapter;").Single().Dump();
}

using (var connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
	connection.Open();
	connection.Execute("drop table if exists data_adapter; create table data_adapter(id integer not null primary key, bytes tinyblob, number integer);");

	using (var adapter = new MySql.Data.MySqlClient.MySqlDataAdapter("SELECT id, bytes, number from data_adapter", connection))
	using (var insertCommand = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO data_adapter(id, bytes, number) VALUES(@id, @bytes, @number);", connection))
	{
		insertCommand.UpdatedRowSource = UpdateRowSource.None;
		insertCommand.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@id", MySql.Data.MySqlClient.MySqlDbType.Int32) { SourceColumn = "id" });
		insertCommand.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@bytes", MySql.Data.MySqlClient.MySqlDbType.TinyBlob) { SourceColumn = "bytes" });
		insertCommand.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("@number", MySql.Data.MySqlClient.MySqlDbType.Int32) { SourceColumn = "number" });

		adapter.InsertCommand = insertCommand;
		adapter.UpdateBatchSize = 100;

		var sw = Stopwatch.StartNew();
		adapter.Update(MakeDataTable());
		sw.Stop();
		sw.Elapsed.Dump();
	}

	connection.Query<long>("select count(*) from data_adapter;").Single().Dump();
}

DataTable MakeDataTable()
{
	var dataTable = new DataTable()
	{
		Columns =
	{
		new DataColumn("id", typeof(int)),
		new DataColumn("bytes", typeof(byte[])),
		new DataColumn("number", typeof(int)),
	},
	};

	var random = new Random(1);
	for (var i = 0; i < 2_000; i++)
	{
		var bytes = new byte[6];
		random.NextBytes(bytes);
		dataTable.Rows.Add(i, bytes, random.Next());
	}
	return dataTable;
}

[bgrainger]

Haven't found a repro; see also mrogunlana/Panama#15 (comment).

[bgrainger]

With a high-latency DB connection, the times are:

• MySqlConnector: 56.4s
• MySql.Data: 2m 54.9s

With adapter.UpdateBatchSize = 100, MySql.Data becomes slightly faster:

• MySqlConnector: 51.7s
• MySql.Data: 45.5s

[bgrainger]

I profiled two ways of inserting 1,000 rows:

	var sb = new StringBuilder();
	var bytes = new byte[6];
	for (int i = 0; i < 1000; i++)
	{
		random.NextBytes(bytes);
		sb.Append($"INSERT INTO data_adapter(id, bytes, number) VALUES({i}, X'{string.Join("", bytes.Select(x => x.ToString("X2")))}', {random.Next()});");
	}

	var sb = new StringBuilder("INSERT INTO data_adapter(id, bytes, number) VALUES");
	var bytes = new byte[6];
	for (int i = 0; i < 1000; i++)
	{
		if (i > 0)
			sb.Append(',');
		random.NextBytes(bytes);
		sb.Append($"({i}, X'{string.Join("", bytes.Select(x => x.ToString("X2")))}', {random.Next()})");
	}
	sb.Append(';');

The former was 1.3s; the latter was 0.015s.

This is the difference between:

INSERT INTO table(columns) VALUES(1, 2, 3);
INSERT INTO table(columns) VALUES(4, 5, 6);
INSERT INTO table(columns) VALUES(7, 8, 9);

and:

INSERT INTO table(columns) VALUES(1, 2, 3), (4, 5, 6), (7, 8, 9);

Being able to transform a batch of INSERT commands from the first format to the second (automatically in MySqlDataAdapter) would make it run a lot faster.

[bgrainger]

The original timing for 2,000 rows with MySqlConnector was 2.59s. With the improvement in 8c582ac, the insert speeds are the following with the respective UpdateBatchSize values:

• 100: 0.085s
• 200: 0.069s
• 500: 0.045s

This is a 30.5× speed-up for an UpdateBatchSize of 100.