Project 702

MySQL vs. SQL Server Express 2012, Comparing Capabilities

I am moving the climate project over to SQL Server, which seems like a great opportunity to compare and contrast a commercial DBMS giant (SQL Server, albeit the Express version) with the open-source MySQL. I think that there is a misconception out there that open-source products are “toys”, not up to the task of professional, commercially-important use. However MySQL is definitely a professional-standard DBMS package used by companies like Yellow Pages, Google and Facebook – so it seems timely that I put my open-source-vs.-proprietary prejudices aside and consider these two from a purely objective standpoint.

The capabilities of SQL Server 2012 (including SQL Server Express 2012) are categorised in full at MSDN.

MySQL Capabilities (that relate to climate project) are below:

MySQL limitations
On the surface, it seems that either product will be more than able to handle the climate chip database. It is interesting that both products have limitations on the RAM, and while this makes sense for SQL Server Express (there has to be a reason why you would pay for the full version), I am surprised that MySQL is limited to 2 GB of RAM.

MySQL Foreign Keys: While not a problem for the climate chip’s single table database, it is worth noting that the default MyISAM database engine does not currently support foreign keys. It will accept foreign key references in a CREATE TABLE statement, but it will not implement them. However, you have the option of the InnoDB engine for this. For more information, see these links:

SQL Server Indexes
I need to look into this a lot more. On the surface, it seems that SQL Server only supports B-Tree indexes. The current climate database uses a B-Tree index on (latitude, longitude) however, as we are essentially only performing lookups, I have proposed that a hash index would provide better lookup performance. Questions to research:

  • Can SQL Server support hash indexes?
  • If not, how does SQL Server implement key-value stores?
  • Do I need to re-address the index structure and simply work on optimising a clustered B-Tree index?
Advertisements

3 thoughts on “MySQL vs. SQL Server Express 2012, Comparing Capabilities

  1. Why do you feel you have a 2GB limitation for RAM? MySQL have a large number of memory settings. The one that will effect performance the most is the innodb bufferpool. I have had to set that to a few hundred gigs to cash the working dataset and I am unaware of a hard limitations.

    Like

    • Hi Ken, sorry you are quite right – it is possible to utilise more than 2 GB of RAM on a single instance. I should have been more specific above, and said that we are limited to 2 GB of RAM per process (on a 32-bit Windows operating system). The way I understand it, you could have multiple processes using as much ram as is available to MySQL, but no single process could use more than 2 GB.

      Like

      • True it is a 32bit memory allocation limit. Your whole OS can not see more then 4GB and even that is through some tricks 😦
        The 64bit version is significantly better. MySQL also runs better under Linux then Windows but the gap is getting smaller and 5.5 64bit performs quite well on windows. Lots of reasons to use MSSQL but few are for performance. Most of them are the easy of use in large diverse enterprise environments already focused on a windows based infrastructure. And a number of advanced T-SQL features simply not available in MySQL. But MySQL does offer huge read performance and very good write performance for the vast majority of applications.
        Also if you are a pure key-value system and wish to use hash indexes try using the Toku engine for MySQL. It is not one of the defaults but provides excellent performance for KV based systems.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s