15

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 used 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.

David Mulder
  • 26,123
  • 9
  • 51
  • 114
TheAJ
  • 10,485
  • 11
  • 38
  • 57
  • This should help with a general comparison of the two, irrespective of C#: http://tinyurl.com/yll4aro – Adam Robinson Nov 18 '09 at 17:56
  • I didn't think this deserved a full answer, but I will comment that I think it's rare that people use the CLR in SQL Server for their core data access. ADO.NET, LINQ, and Entity Framework seem to be the main choices. – Bernard Chen Nov 18 '09 at 18:02
  • If you are interested in using LINQ then you don't really need to know much about SQL Server. The whole purpose is that LINQ abstracts data access so you don't directly write SQL queries. – Dan Diplo Nov 18 '09 at 18:47
  • 2
    @AdamRobinson Ironically I wrote it to Google and I got here. – totymedli Jan 04 '13 at 09:29

7 Answers7

54

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. :-)

Justin Grant
  • 44,807
  • 15
  • 124
  • 208
  • 1
    Remember that the Web Edition is solely for public-facing web apps or sites, not intranets. – MartW Nov 18 '09 at 18:35
  • 1
    I'll add that mySQL can be faster for simpler queries. Another option is PostGreSQL, it's in between the 2. It can be scaled to Enterprise sizes just like (some say better than) MS SQL server. Doesn't cost a bazillion dollars like SQL Server can (very much use dependent anyways). – Brian Knoblauch Nov 18 '09 at 18:38
  • 2
    Yep. I've been using Postgres too recently. The DBMS is fine, but my main complaint is chicken-and-egg: Postgres has a smaller user base, so other products (e.g. SubSonic) integrate with Postgres later than other DBMS's. And if I want to use Entity Framework with Postgres, I may need to shell out $100's for dotConnect. And so on. But especially given the Oracle concerns, if I were choosing an open-source DB today, Postgres may be a safer long-term bet. – Justin Grant Nov 18 '09 at 18:42
  • 1
    +1, but a drawback is all of the TSQL specific nonsense. Becomes a nightmare for portability. – user7116 Nov 18 '09 at 20:16
  • @sixlettervariables - agreed, but my porting experience from MySQL to Postgres was not great either-- not only for SQL language variations but also for how you need to build queries to generate good plans and work around DBMS-specific issues. The DB-neutral ORM approach is beginning to look more and more attractive. :-) – Justin Grant Nov 18 '09 at 21:44
  • @sixletter: That's true of *any* RDBMS that includes a procedural language. – Adam Robinson Nov 19 '09 at 13:39
4

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.

womp
  • 115,835
  • 26
  • 236
  • 269
2

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.

Rob Ferrante
  • 31
  • 1
  • Your last comment about having application logic in your application code is the prevailing wisdom. I've worked on many enterprise applications that have a lot of the data processing code in stored procedures and it works fine and is faster than having it in code. – infocyde May 21 '13 at 21:46
1

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.

stimms
  • 42,945
  • 30
  • 96
  • 149
  • 4
    LINQ to SQL is very much tied to SQL Server. – LukeH Nov 18 '09 at 17:52
  • Eh, kinda. There's no official MS support for linq to mysql, but there's an open source project out there that has a mysql provider. – Chris Van Opstal Nov 18 '09 at 17:57
  • @Chris: If you get into the realm of "hackery" (this term sounds more negative than I intend), then all bets are off. In terms of true LINQ-to-SQL (compared to, say, the Entity Framework), it's directly tied to SQL Server. In fact, it even has issues with SQLCE. – Adam Robinson Nov 18 '09 at 18:16
  • Yes, LINQ to SQL is very much tied to SQL Server. But the answer you're commenting on didn't say LINQ to SQL, it said LINQ. And the answer is correct, LINQ is not tied to a specific database, or even to a database at all. The MySQL provider for .NET supports Entity Framework, so it is possible to use LINQ with MySQL without any hackery at all. – Joel Mueller Nov 18 '09 at 19:11
  • @Joel: You're correct, but it's been my (unfortunate) experience that most of the time people are referring to LINQ (especially those that are unfamiliar with it) they are referring to LINQ-to-SQL. – Adam Robinson Nov 19 '09 at 13:40
1

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.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
1

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.

Ryan Elkins
  • 5,731
  • 13
  • 41
  • 67
1

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.

Kuberchaun
  • 29,160
  • 7
  • 51
  • 59