We’ve been debating how to approach SQL data access in an upcoming project. Previous experience with NHibernate and Entity Framework was disappointing in terms of performance, for two main reasons:

  1. Badly structured queries generated by the ORM, combined with no easy control over query structure (for some complex queries, EF especially did bad)
  2. Slow processing of SQL results to C# instances

There were other problems as well but we’ll touch the performance issue here.

For the first problem - badly structured SQL - your options are:

  1. Hack around the limitations of the ORM (such as this one for NHibernate 3.0, or adding specific views in the database and selecting from those)
  2. Wait for improvements in the next release (EF have improved their queries’ structure in some of the releases, but we didn’t yet upgrade all our projects to the latest release, so we don’t know yet how it affects us)
  3. Use a lower-level “micro-orm” (such as Dapper) that allows you greater control over the query structure, but lose some important features (such as type-safe query definition using LINQ and simpler API for writes)

As for the second problem - slow processing of the SQL results - this again can be solved by switching to a more performance-oriented “micro-orm”. We were considering to switch from EF to Dapper which takes prides in its performance. Since we have reasons to stick with EF (type-safe queries being a main point), I decided to test the performance of the latest EF release using the same benchmark used in the Dapper charts.

Turns out, the main performance gains come from simply switching to .NET 4.5. Not only is EF’s performance boosted by over 80%, also other ORMs (including NHibernate, although to a lesser degree) are faster when running on .NET 4.5 as compared to .NET 4. So here are the results. I performed these tests using Dapper’s test program by just running them on my Visual Studio 2010 with standard .NET 4, then installing .NET 4.5 RC, rebuilding, and running them again.

[caption id=”attachment_111” align=”aligncenter” width=”1024”]ORM Test query time, .NET 4 vs. 4.5 (click for full-sized image)[/caption]

(Disclaimer: this is based on the Dapper test, and assumes that it’s a fair, relevant test for ORM query performance)

With .NET 4, EF and NHibernate are orders of magnitude slower than Dapper (and other fast frameworks). But with .NET 4.5, the difference is not so great (a factor of 2) and it may not be worth abandoning the high-level feature for such a slow down. Of course this depends on use case, requirements, etc.

Here are the same results in terms of performance improvement (calculated as: (original - new) / original):

[caption id=”attachment_112” align=”aligncenter” width=”1024”]% Improvement (.NET 4 to 4.5) (click for full-sized image)[/caption]

Bottom Line

This is addressed to EF users mainly. If you’re worried about performance and:

  1. You don’t have many complex queries or don’t mind hacking around to get better query structure out of EF
  2. Or, most of your performance issue are when a simple query fetches a lot of data
  3. Or, the features that EF offers are important to you
  4. And you can wait for .NET 4.5 to be released (I couldn’t find a release date anywhere, but I’m guessing it’s tied to Visual Studio 2012 or Windows 8, should be by end of 2012)

…then you can stick around with EF.

However if the ORM performance is an absolutely critical bottleneck, you should still consider using something like Dapper which will give you greater control over query structure and processes results twice as fast as EF even under .NET 4.5, out-of-the-box.

P.S. If you’re using EF’s code-first with the fluent interface for mapping (rather than attributes), you’ll be able to switch to another solution in case you need to. This is a point against the pretty designer-based EDMX model-first approach. I like the clean classes being separate from the database-related code, so I use code-first anyway.