Re: mysql .net performance
Hello!
Just a bit more salt to discussion.
I have run extended benchmarks in comparison to the above ones with one insert statement resembling stuff which cuold be found quite often on the web.
INSERT comprises 6 varchar values with length up to 45 char which are performed on empty tables. MySql uses InnoDB table types as I wanted to compare DBs which support transactions. Database engine used is MySQL 4.1.4 gamma-nt-max via TCP/IP. Have not tested named pipes yet.
The measurements were done using processor ticks rather than .NET DateTime struct ticks with help of my Math library utility functions which are to be released soon on Sourceforge.
Here are the results for given operation expressed in processor ticks:
Connection Open Close INSERT
.NET MS SQL 114 290 47 636 2 030 712
ByteFX MySQL 1 340 837 175 319 37 336 848
MySql Connector 13 132 318 182 722 37 105 913
Measurment is an arithmetic average of 1000 operations performed according to code presented below.
One can spot huge differences easily. What is striking this is extremly poor connection opening performance with MySql Connector (100x slower than MS SQL and 10x slower than old ByteFX). Another major difference is INSERT time which is 18x slower with MySQL. This could be attributed perhaps to poor default databse configuration which anyway should be better tuned before release.
My hope is that MySql Connector will improve significantly in performance so I could use it in production environment. Current benchmark results exclude MySQL from any production use with ASP.NET/Web Services on IIS (unfortunately Mono is not stable enough to be regarded as a production grade framework) and any .NET applications requiring easy scaling.
Another drawback is a SQL syntax change introduced with MySql Connector which breaks my old apps (all my SQL commands worked so far on Oracle 8/9/10 i, MS SQL and MySQL). I am really against that kind of SQL syntax mangling and this may convince me to go with other databases in .NET rather than with MySQL. My hope however is that this can be changed in favor of many developers working with multiple databses.
Cheers,
Jacek
Code follows:
private static double SQLTimings()
{
SqlConnection conn = new SqlConnection("connection string");
SqlCommand command = new SqlCommand("", conn);
int i = 0;
ulong start = JB.Math.Utilities.GetProcessorTicks();
ulong end = 0;
ulong connOpenTiming = 0;
ulong connCloseTiming = 0;
ulong commInsertTiming = 0;
ulong connStart = 0;
ulong startQ = 0;
ulong interQ = 0;
try
{
//Clear table before test
command.CommandText = "TRUNCATE TABLE Users;";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
for ( ; i < Tools.SqlTestRuns; i++)
{
command.CommandText = "INSERT INTO Users Values('"
+ "a"+ i.ToString() + "','"
+ "aaaaaaaa" + "','"
+ "email" + "','"
+ "auctionUser" + "','"
+ "auctionPassword" + "','"
+ "bbbbbbUser" + "','"
+ "bbbbbbPassword"
+ "');";
connStart = JB.Math.Utilities.GetProcessorTicks();
conn.Open();
startQ = JB.Math.Utilities.GetProcessorTicks();
command.ExecuteNonQuery();
interQ = JB.Math.Utilities.GetProcessorTicks();
conn.Close();
connCloseTiming += JB.Math.Utilities.GetProcessorTicks() - interQ;
connOpenTiming += startQ - connStart;
commInsertTiming += interQ - startQ;
}
end = JB.Math.Utilities.GetProcessorTicks();
// Clear table after test
command.CommandText = "TRUNCATE TABLE Users;";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
catch(SqlException ex)
{
string errorMessages = "";
for (int j=0; j < ex.Errors.Count; j++)
{
errorMessages += "Index #" + j + "\n" +
"Message: " + ex.Errors[j].Message + "\n" +
"LineNumber: " + ex.Errors[j].LineNumber + "\n" +
"Source: " + ex.Errors[j].Source + "\n" +
"Procedure: " + ex.Errors[j].Procedure + "\n";
}
Console.WriteLine("Exception was thrown:\n"
+ errorMessages + "\n"
+ ex.ToString());
}
finally
{
conn.Close();
}
Console.WriteLine("SQL SqlConnection.Open() timing: {0} ticks.", connOpenTiming/(uint)Tools.SqlTestRuns);
Console.WriteLine("SQL SqlConnection.Close() timing: {0} ticks.", connCloseTiming/(uint)Tools.SqlTestRuns);
Console.WriteLine("SQL INSERT command timing: {0} ticks.", commInsertTiming/(uint)Tools.SqlTestRuns);
return ((double)(end - start))/Tools.SqlTestRuns;
}
private static double MySQLTimings()
{
ByteFX.Data.MySqlClient.MySqlConnection conn = new ByteFX.Data.MySqlClient.MySqlConnection("connection string");
ByteFX.Data.MySqlClient.MySqlCommand command = new ByteFX.Data.MySqlClient.MySqlCommand("", conn);
ulong start = JB.Math.Utilities.GetProcessorTicks();
ulong end = 0;
int inserts = 0;
ulong connOpenTiming = 0;
ulong connCloseTiming = 0;
ulong commInsertTiming = 0;
ulong connStart = 0;
ulong startQ = 0;
ulong interQ = 0;
try
{
command.CommandText = "TRUNCATE TABLE Users;";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
for (int i = 0; i < Tools.SqlTestRuns; i++)
{
command.CommandText = "INSERT INTO Users Values('"
+ "a" + i.ToString() + "','"
+ "aaaaaaaa" + "','"
+ "email" + "','"
+ "auctionUser" + "','"
+ "auctionPassword" + "','"
+ "bbbbbbUser" + "','"
+ "bbbbbbPassword" //+ "',"
//+ i.ToString()
+ "' );";
connStart = JB.Math.Utilities.GetProcessorTicks();
conn.Open();
startQ = JB.Math.Utilities.GetProcessorTicks();
inserts += command.ExecuteNonQuery();
interQ = JB.Math.Utilities.GetProcessorTicks();
conn.Close();
connCloseTiming += JB.Math.Utilities.GetProcessorTicks() - interQ;
connOpenTiming += startQ - connStart;
commInsertTiming += interQ - startQ;
}
end = JB.Math.Utilities.GetProcessorTicks();
command.CommandText = "TRUNCATE TABLE Users;";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
catch(Exception ex)
{
Console.WriteLine("Exception was thrown:\n" + ex.ToString());
}
finally
{
conn.Close();
}
Console.WriteLine("ByteFX MySqlConnection.Open() timing: {0} ticks.", connOpenTiming/(uint)Tools.SqlTestRuns);
Console.WriteLine("ByteFX MySqlConnection.Close() timing: {0} ticks.", connCloseTiming/(uint)Tools.SqlTestRuns);
Console.WriteLine("ByteFX INSERT command timing: {0} ticks.", commInsertTiming/(uint)Tools.SqlTestRuns);
return ((double)(end - start))/Tools.SqlTestRuns;
}
public static double MySqlConnectorTimings()
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection("connection string");
MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand("", conn);
ulong start = JB.Math.Utilities.GetProcessorTicks();
ulong end = 0;
int inserts = 0;
ulong connOpenTiming = 0;
ulong connCloseTiming = 0;
ulong commInsertTiming = 0;
ulong connStart = 0;
ulong startQ = 0;
ulong interQ = 0;
try
{
command.CommandText = "TRUNCATE TABLE Users;";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
for (int i = 0; i < Tools.SqlTestRuns; i++)
{
command.CommandText = "INSERT INTO Users Values('"
+ "a" + i.ToString() + "','"
+ "aaaaaaaa" + "','"
+ "email" + "','"
+ "auctionUser" + "','"
+ "auctionPassword" + "','"
+ "bbbbbbUser" + "','"
+ "bbbbbbPassword" //+ "',"
//+ i.ToString()
+ "' );";
connStart = JB.Math.Utilities.GetProcessorTicks();
conn.Open();
startQ = JB.Math.Utilities.GetProcessorTicks();
inserts += command.ExecuteNonQuery();
interQ = JB.Math.Utilities.GetProcessorTicks();
conn.Close();
connCloseTiming += JB.Math.Utilities.GetProcessorTicks() - interQ;
connOpenTiming += startQ - connStart;
commInsertTiming += interQ - startQ;
}
end = JB.Math.Utilities.GetProcessorTicks();
command.CommandText = "TRUNCATE TABLE Users;";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
catch(Exception ex)
{
Console.WriteLine("Exception was thrown:\n" + ex.ToString());
}
finally
{
conn.Close();
}
Console.WriteLine("MySQL Connector.NET MySqlConnection.Open() timing: {0} ticks.", connOpenTiming/(uint)Tools.SqlTestRuns);
Console.WriteLine("MySQL Connector.NET MySqlConnection.Close() timing: {0} ticks.", connCloseTiming/(uint)Tools.SqlTestRuns);
Console.WriteLine("MySQL Connector.NET INSERT command timing: {0} ticks.", commInsertTiming/(uint)Tools.SqlTestRuns);
return ((double)(end - start))/Tools.SqlTestRuns;
}