C# - MySQL vs Microsoft SQL Server [closed]
For the longest time, I've been using MySQL servers to handle data (in JAVA, and in C#). But lately, I've been hearing good things about LINQ and SQL Server. I've been thinking of converting, but I don't know much about the SQL Server.
Could anyone who has 开发者_如何学Goused SQL Server before, please define how well it is compared to MySQL server in terms of performance and usability.
I've also heard that SQL Server would be better for C# as it's basically built in.
I have used SQL Server for many years on C# projects large and small, but have been using mostly MySQL for the last year on various C# (but open-source-related and startup-related) projects which were already using MySQL.
I miss SQL Server! In my experience, SQL Server is better in many ways:
- the query optimizer in SQL Server is smarter, meaning that you can often build queries and they'll produce optimal query plans. With MySQL, I find myself spending more time hand-tuning even relatively simple queries in order to produce good query plans.
- the underlying database engine in SQL Server can do a wider variety of things to boost performance. for example, all joins in MySQL are Nested Loop joins, while SQL Server can do Hash Joins or Merge Joins which can sometimes boost query performance 10x+. SQL Server can also parallelize queries which, for large data-warehouse workloads especially, can dramatically boost performance.
- the GUI tools are miles ahead. SQL Server's graphical plan query optimizer makes query optimization a snap-- you'll never want to go back to EXPLAIN EXTENDED. SQL Server 2008's graphical monitoring tools are so much easier than digging through the slow query log to figure out what's going wrong. And so on.
- As you mentioned, the .NET integration story (C#, Linq, Entity Framework, etc.) in SQL Server is better. I use C#, Entity Framework, and LINQ with MySQL too, so it's not an either-or thing, although performance is likely to be better with SQL Server in a .NET environemnt because the teams work together to boost performance and make integration work better.
- SQL Server's SQL-language support is richer than MySQL's, including some very cool features (in SQL 2008 especially) like
ROW_NUMBER(),GROUPING_SETS,OPTIMIZE FOR, computed columns, etc. - Backup is many times faster, especially in SQL 2008 with compressed backups
- There's no Oracle acquisition cloud hanging over the future of SQL Server.
- SQL Server (especially the expensive editions) come with other goodies, like an OLAP data warehouse (SSAS), a reporting solution (SSRS), an ETL tool (SSIS), a scheduler (SQL Agent), etc. You can get similar open-source tools, for free (e.g. Pentaho, BIRT, etc.) but integration tends to be better with SQL Server.
That said, there are significant drawbacks, which may or may not be deal-breakers for you:
- you're stuck using Windows Servers, with all the pluses and minuses this entails
- SQL Server, especially the higher-end editions, are expensive! For small DB's (<4GB I think), SQL Server Express is free, though, and is nearly as full-featured as the regular SQL Server-- if you know your data is going to be small and you know your boss is a cheapskate, Express is the way to go. Also, there's a new SQL Server 2008 Web Edition which, for internet-facing web apps, should theoretically offer cheap hosting since the cost to a hoster is only $15/month per processor.
- It's not open source. Some companies and development teams are very passionate about this, for good reasons (debugging, cost, philosophy, etc.) !
- related to above: if you want to get a bug fixed in MySQL, and you've got the skills, you can fix it yourself. With SQL Server, there are painful bugs in query processing, optimization, etc. that persist for years-- I've spent an absurd amount of time working around some of those.
- for very simple, read-only (or non-transactional) workloads (e.g. a DB-based cache access from a web app) where you can get away with using MyISAM instead of InnoDB, I hear that MySQL can be significantly faster.
Caveat: I hear that MySQL 6.0 is supposed to address many of the gaps and differences above, but I admittednly haven't kept myself up to speed with how the Oracle thing, etc. will affect the schedule and/or featureset.
re: your "C# is built-in" note: yes, you can develop stored procedures, functions, aggregates, etc. using .NET languages, but IMHO in most scenarios this is more trouble than it's worth, including because deployment is harder and DBAs are less comfortable with .NET code on their servers. The real win for a C# + .NET + Visual Studio + SQL Server combination, IMHO, is that they have been designed in parallel over the last 10 years to all work well together, so you'll get ease of use and synergy that you may not get using MySQL. That said, as I noted above, this isn't a deal-breaker or deal-maker... it's just smoother using SQL Server with the rest of the Microsoft stack.
In summary, let me be clear that, for many DB workloads, MySQL is good enough-- it works, it's stable, it's fast, it has reasonably good tools, etc. And it's affordable! :-) I would never refuse a project simply because they're using MySQL. But the comparison is like driving a Honda vs. a BMW... the Honda gets you where you want to go, but if your wallet can take it, you'll enjoy the ride a lot more with the Bimmer. :-)
I use both pretty regularly, and am currently studying for my Sql Server MCTS, so I can probably throw a few useful comments in here.
SQL Server is a much, much, much more fully-featured database, especially the 2008 version. MySQL doesn't even support column constraints (for example, creating an int column and then constraining the value to between 1 and 1000 - can't enforce that automatically in MySQL). SQL Server offers full-text indexing, native XML columns and manipulation, multiple transaction modes, complex security offerings, replication and distributed features, and a great management suite.
In regards to your C# comment, yes, SQL Server has the ability to import CLR objects, meaning that you can compile some .Net code and drop it into SQL Server and have database functions that use it. This is especially useful for creating new aggregate functions, as you can bypass cursors and use faster CLR code loops.
You can also optimize SQL Server to the hilt, down to the locking and isolation modes that it uses, and the CPU/memory that a given thread can use.
MySQL, on the other hand, is free and relatively easy to set up, with enough options that most website owners are pretty happy using it for their basic CRUD apps.
If you wish to use Linq2Sql, then you need SQL Server. If you need tons of advanced features, SQL Server is the way to go for sure.
I have worked with both, though far more with SqlServer. In a large shop, especially one where many application architectural decisions arise from DB Architects, SQL Server offers more. Replication, SSIS, etc. But, those add a LOT of complexity, and in my experience have been the source of a good portion of operational outages.
But as a datastore for a software application, MySql does what you need and is straightforward, reliable, fast and simple.
Speaking strictly philosophically, you really want your application logic in your application code, not in complex SQL and constraint definitions. Go for the basics in your datastore, and put the time you save into the program itself.
LINQ is a language in its own right and isn't tied to a specific database or even to a database. Really it is just a tool for the manipulation of structured data collections. The underlying language in which the DBMS is written should not matter in terms of what language you use it with.
C# would access MySQL and SQLServer basically identically using ADO.NET (or LINQ - LINQ to SQL is tied to SQL Server, but it doesn't appear to have a long life ahead of it), so that wouldn't be a factor in my mind, although IDE integration might be.
The benefits of SQL Server would include the more complete gamut of development support within SQL Server, the Agent and scheduling, the security model, and indexing, tuning and other features like Integration Services, Reporting Services and Analysis Services which make it relatively easy to manage large system infrastructures.
I don't think you've given enough information about your system to make the choice between them obvious at this point.
From a development (coding) standpoint I have worked with both as a Java developer and a C# developer (connecting to both on various projects, so Java to SQLServer, Java to MySQL, C# to MySQL, C# to SQLServer). Personally I didn't really notice that much of a difference although I wasn't doing anything too complex. I don't think there's too much to worry about if you're thinking there will be a steep learning curve unless you want to get into some of real nitty gritty server specific stuff. There are some minor differences, but nothing that someone wouldn't pick up on quickly.
Have you considered a more full featured open source database like PostgreSQL. There's nothing wrong with SQL Server, but it does start to hook you into a licensed piece of software that you can stay away from if you remain with an open source counter part. It all depends if that is ok with you.
加载中,请稍侯......
精彩评论