DB Connections in ASP.NET, MySQL vs. SQL Server

Question

I'm using MySQL in an ASP.NET project I'm currently working on, I did some tests to test the performance of the MySQL .NET provider but unfortunately I'm not very pleased with the results.

A very simple loop that only opens the connection was 10x faster in SQL Server:

// MySQL

const string CONNECTION_STRING = 
"server=localhost;database=testdb;user id=root;password=mypassword;max pool size=250;";
for (int i = 0; i < 5000; i++)
{
  using (MySqlConnection con = new MySqlConnection(CONNECTION_STRING))
  {
    con.Open();
  }
}

// SQL Server

const string CONNECTION_STRING = "Data Source=localhost;Initial Catalog=testdb;Integrated Security=True;max pool size=250;";
for (int i = 0; i < 5000; i++)
{
  using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
  {
    con.Open();
  }
}

SQL Server is much faster in everything else too (selects, updates, inserts .. etc). Am I doing anything wrong? is there any server variables I should change?

More info:
- I run MySQL on Windows (5.0.51a-community-nt)
- SQL Server 2005 was used in the test
- Specs: Windows XP SP2, CPU Intel 1.6GHz Dual Core, 1024 MB RAM

This is the configuration of MySQL:

[client]
port     = 3306
socket   = /tmp/mysql.sock

[mysqld]
port     = 3306
socket   = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8

server-id   = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Thanks for any suggestions ...

Answer 1

SQL server uses connection pooling by default: each connection opened with the exact same connection string is returned to the pool when closed. Returning a connection from the pool is much more efficient than creating a connection from scratch. I assume MySql doesn't provide connection pooling by default

Answer 2

It might be connection pooling, but you also have to consider the fact that when .NET code is talking to Sql Server, it's communicating using the most efficient possible channels, since the code is all owned and controlled by Microsoft. If .NET code is talking to a 3rd party server, or if some 3rd party code is talking to Sql Server, the communication has to be more standards-based and generic, therefore it's much slower.

That's why back during the "Pet Shop" wars, the ASP.NET solution always ran so much faster than the alternate solutions. When you're forced to be platform-agnostic, you can't possibly keep up with 100% native code.

Answer 3

Something to think of is how mySQL was installed, did you tell it that the server was a dedicated DB server, or a workstation? MySQL can be configured to restrict itself so it doesn't conflict with other services, or to take whatever is available. SQL Server will use any resources it thinks it need. I would look into how your mysql server was configured, there is a good chance that you could get some performance improvements there.

Finally windows isn't the platform of choice for mysql, it's supported but if you really want it to perform put it on a linux system where you can tweak the File system, etc. to get the best performance, something that SQL Server just can't do

here is a link to some articles, blogs, books, etc for performance tuning mysql
http://forums.mysql.com/read.php?24,92131,92131