Bug #48243 sql insert is slow with connector 6.x and compression enabled
Submitted: 22 Oct 21:28 Modified: 29 Oct 10:39
Reporter: Christian Mueller
Status: Verified
Category:Connector/Net Severity:S3 (Non-critical)
Version:6.0.4, 6.1.2, 6.2.0 OS:Any
Assigned to: Reggie Burnett Target Version:
Triage: D2 (Serious)

[22 Oct 21:28] Christian Mueller 
Description:
SQL INSERT is significant slower on connector 6.x compared to 5.x when compression is
enabled.

How to repeat:
run the following code and compare the times between 6.x and 5.x

string connectString = "Database=test;Charset=utf8;Compress=true;Convert Zero
Datetime=true";
MySqlConnection con = new MySqlConnection( connectString );
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = con;
cmd.CommandText = "DROP TABLE IF EXISTS t1";
cmd.ExecuteNonQuery();
cmd.CommandText =
	@"CREATE TABLE t1 (
		id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
		ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		dt DATETIME,
		ch VARCHAR(255) CHARACTER SET utf8,
		bn VARBINARY(255),
		tx TEXT CHARACTER SET utf8
	)"
;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO t1 (dt,ch,bn,tx) VALUES(?dt,?ch,?bn,?tx)";
cmd.Parameters.Clear();
cmd.Parameters.Add( "?dt", MySqlDbType.DateTime );
cmd.Parameters.Add( "?ch", MySqlDbType.VarChar );
cmd.Parameters.Add( "?bn", MySqlDbType.VarBinary );
cmd.Parameters.Add( "?tx", MySqlDbType.Text );
cmd.Prepare();
for( int i = 1; i <= 100; i++ ) {
	cmd.Parameters[0].Value = DateTime.Now;
	cmd.Parameters[1].Value = i.ToString() + new String( 'X', 200 );
	cmd.Parameters[2].Value = Encoding.ASCII.GetBytes( i.ToString() + new String( 'X', 200 )
);
	cmd.Parameters[3].Value = new String( 'X', 16384 );
	cmd.ExecuteNonQuery();
	Console.WriteLine( "insert row {0}", i );
}
[29 Oct 10:39] Tonci Grgin 
Hi Christian and wow!

Verified just as described. Using same test case, c/NET 6 trunk driver is ~10 times
slower than 5.2 one. Removing compression yields, in terms of speed, expected results
(aprox. 20 times faster). Tests are done using freshly built debug and release
libraries.

Test compr. 5.2: 00:00:00.6230070
Test compr. 5.2: 00:00:00.5692995
Test compr. 5.2: 00:00:00.5673465
Test compr. 5.2: 00:00:00.6034770

Test compr. 6.2: 00:00:07.0210350
Test compr. 6.2: 00:00:06.9790455
Test compr. 6.2: 00:00:06.9731865
Test compr. 6.2: 00:00:06.9790455