Here's the short version:
- This is only for inserting 500 rows into a 3 column table
- InnoDB really seems screwed on Windows platforms running 5.x - Certainly don't have anything conclusive, but I'd watch out
- For my very simple test, MySQL on Linux blew SQL Server 2005 on windows out of the water.
- On windows, ASP.NET's connection to MySQL seems as fast as PHP's
Here's the long version
For a large upcoming project, we've pretty much decided on a .NET application and MySQL database. We'll be using a database cluster for our primary work and smaller database servers to handle secondary tasks (i.e., set up logging as a simpler replication solution).
Before jumping head-first, I wanted to play with the 5.0 beta connector/NET drivers from MySQL. Previously I've only used the ODBC drivers to connect to MySQL and wasn't impressed. I pretty much wanted to make sure it worked like I thought it should (which it does) and that there weren't gonna be any major performance pitfalls. Everything was running locally on my machine - I know, not ideal, but I figured it'd be good enough to catch anything specific I might need to look at more closely (and I was right).
I created a simple table with 3 columns : autoincrement int as the PK, varchar column, an datetime column. My first test was simple - loop 500 times and insert a record ala:
VALUES ("Random Name 34", Now())
I didn't see much of a difference between opening/closing the connection for each insert (thank you connection pooling), keeping the connection open outside of the loop or using a stored procedure. In all cases, the code ran....REALLY REALLY slowly - 13 seconds to be exact!.
I knew there was no way for 500 inserts to take 13 seconds, so I wrote the same code for SqlClient, set up SQL Server 2000 and the test ran in 0.3 seconds (again, all locally).
At this point, my Linux-friendly boss got very interested. I was really worried that the connector might be a POS, so I wrote the same code in PHP and got the same result - good news for .NET, but still baffled.
I finally noticed the table type was InnoDB. I switched it to MyISAM and all the problems went away. All in all, it's safe to say that locally on my windows machine, SQL Server ran a bit faster than MySQL (say 0.28 seconds vs 0.3)
We decided to test this in a linux environment. My boss wrote the same code for our powerful linux development server and got things rolling using PHP. MyISAM was running at a blazing 0.05, InnoDB at 0.2. Instead of being about 35x slower, the InnoDB storage type was only 4 times slower.
We were really interested to see how the .NET / SQL Server solution on the same hardware would compare. Luckily, that was easily possible on our Windows development server. Would we see the same 6x improvement in performance from the better hardware? Sadly not. The .NET / SQL Server code ran at 0.25 seconds...a very marginal increase.
What made MySQL run so fast? Was it the hardware? Why couldn't SQL Server (2005 Express on the dev machine) take advantage of the hardware as well?Was it because it was running on Linux (that certainly seems to be the case with InnoDB).
Given the same hardware, it would be nice to get SQL Server running in the 0.0X range like MySQL. Even if this was an accurate test (you know, for all I know it's 1 checkbox somewhere in SQL Server and zooom), each database will handle different tasks differently. Tomorrow we'll try connecting .NET with MySQL on the linux box and hopefully get the same results. So far so good for the Connector/NET :)