Fetch performance of various .NET ORM / Data-access frameworks, part 2

This is the second post about fetch performance of various .NET ORM / data-access frameworks. The first post, which has lots of background information can be found here. In this second post I'll post new results, including results from frameworks which were included after the previous post. The code used is available on GitHub. I'd like to thank Jonny Bekkum for adding benchmark code for many of the frameworks which were added after the previous post.

Entity Framework

In the previous benchmark run, it clearly showed that Entity Framework 6 and NHibernate were, well… slow. I filed a bug with the Entity Framework team and they created a workitem for it for v6.1. They were able to make Entity Framework perform better by 20-30%, but only in the situation where foreign key fields were present in the model. I discovered that that wasn't the case in the previous setup. The performance tests shown today are with foreign key fields present as other frameworks fetch these too and it's only fair that they're present to get a full picture. If they're not included, performance of v6.1 is slightly faster than 6.0.2 (~3000ms in 6.0.2 to ~2830ms in 6.1).

Telerik Data Access

New in this benchmark is, among others, Telerik DataAccess. Jonny added two versions, one using normal domain mappings and one using the code-first Fluent Mappings. These mappings were generated using the Telerik Wizard. However, this wizard creates dreadful code: the generated mappings class contains a vital flaw: without altering the code, it will create the meta-data model every time which obviously is tremendously slow. See the class for details (line 34-41)

The setup

Since the previous setup, I've replaced my old server with a new one and have installed SQL Server 2012 on the OS itself instead of in a VM so the results are little better overall than before because of this.

  • Server: Windows Server 2012 64bit, SQL Server 2012 on i5-4670 @ 3.4ghz with 8GB ram, raid 1 HDDs.
  • Client: Windows 8.0 32bit, .NET 4.5.1 on a Core2Quad @ 2.4ghz with 4GB ram
  • Network: 100BASE-T
  • Database: AdventureWorks 2008
  • Entity Fetched: SalesOrderHeader

For a discussion about this setup and why it is done this way, please see the first post.

The code

The code has been drastically improved since the first post. It's now a properly designed application with a runner and easy to implement benchmark classes so it's easy to add another framework to the system. Please have a look at the code at the github repository.

The results

The raw results can be found here. They include the full output as well as the aggregated results in a nice list. Additionally to the previous benchmark run, individual fetch tests have been added as well as tests how fast enumerating the fetched collection is. This is done to discover which frameworks offload work to when the program actually consumes the result-set which can hide the real performance of the set fetch if this isn't taken into account. The Entity Framework v6.1 results are from a separate batch run before the results seen below, and are solely meant to illustrate progress made in v6.1.

With change tracking (10 runs, averaged, fastest/slowest ignored)

'With change tracking' is as the name implies, a fetch of elements which can be used in read/write scenarios: the changes made to the data are tracked and can be easily persisted. In general this takes some extra work (not much though, if you're clever Winking smile)

Framework, with change-tracking Fetch avg. (ms) Enumeration avg. (ms)
DataTable, using DbDataAdapter 532.63 52.00
Linq to Sql v4.0.0.0 (v4.0.30319.18408) 638.75 2.75
LLBLGen Pro v4.1.0.0 (v4.1.14.0117) 685.25 10.75
Telerik DataAccess/OpenAccess Domain v4.0.3 1197.38 3.00
Telerik DataAccess/OpenAccess Fluent v4.0.3 1218.38 3.00
Oak.DynamicDb using typed dynamic class 1304.13 1624.88
NHibernate v3.3.1.4000 (v3.3.3.4001) 3910.13 4.00
Entity Framework v6.0.0.0 (v6.1.30207.0) 4081.63 3.00
Entity Framework v6.0.0.0 (v6.0.21211.0) 6701.38 3.00

Clearly we see that a typed dynamic class is very slow during enumeration (Oak.DynamicDb enumeration average). For more info about this, see this post by Oak developer Amir Rajan. It also shows that Microsoft made good progress in Entity Framework v6.1 but is still far off from what people expect from it, especially when you think about the fact that Linq to Sql's performance is almost 10 times faster (let that sink in for a minute) than it's bigger brother Entity Framework v6.0.2.

Without change tracking (10 runs, averaged, fastest/slowest ignored)

Without change tracking is a fetch to read-only elements: changes to the data (if possible) are not tracked, so if you use these fetches in read/write scenarios you have a hard time persisting changes made unless you do a lot of work. This usually leads to faster fetches, as the work to make sure changes are tracked can be avoided. This is in general the territory of the Micro-ORMs which are simply there to do one thing: read data as fast as possible into objects and nothing else.

Framework, without change-tracking Fetch avg. (ms) Enumeration avg. (ms)
Handcoded materializer using DbDataReader 500.38 2.00
PetaPoco Fast v4.0.3 502.00 2.00
PetaPoco v4.0.3   515.88 2.00
Dapper 519.38 2.00
Linq to Sql v4.0.0.0 (v4.0.30319.18408) 561.75 2.88
Entity Framework v6.0.0.0 (v6.0.21211.0) 564.75 2.13
ServiceStack OrmLite v4.0.9.0 (v4.0.9.0) 589.75 2.00
LLBLGen Pro v4.1.0.0 (v4.1.14.0117), typed view 736.00 5.00
Oak.DynamicDb using dynamic Dto class 1269.50 199.50

Here we see that none of the frameworks can beat the hand-written materializer. This is not a surprise as all these frameworks have to do some work to make sure the data fits in an instance of a type they generate at runtime in some cases. LLBLGen Pro's Typed Views (which are typed datatables here) are not doing so well, even though the DataTable fetch in the previous table was very fast, and it even gets beaten by the change tracked entity fetches. A reason for this is that the projection code currently uses a pipeline which can handle any row at any given moment, but for set fetches which expects each row to have the same amount of fields, it's overhead which can be done without, however this requires architectural changes which I can't make mid-release.

Individual fetches with change tracking (100 individual fetches, 10 runs)

This benchmark fetches change tracking elements (see above) but this time it fetches 100 elements, one element at a time. Although the times taken by most frameworks are very low, it gives another insight in what a framework is doing. 

Framework, with change-tracking, individual fetches individual fetch avg. (ms)
Telerik DataAccess/OpenAccess Domain v4.0.3 0.67
Oak.DynamicDb using typed dynamic class 0.67
Telerik DataAccess/OpenAccess Fluent v4.0.3 0.69
DataTable, using DbDataAdapter 0.73
LLBLGen Pro v4.1.0.0 (v4.1.14.0117) 1.19
NHibernate v3.3.1.4000 (v3.3.3.4001) 1.33
Entity Framework v6.0.0.0 (v6.0.21211.0) 2.44
Linq to Sql v4.0.0.0 (v4.0.30319.18408) 2.64

Comparing this table with the one which fetches a set, we see a different picture. Here we see the Telerik code performing much better than when fetching a set, which is also true for NHibernate. Linq to Sql was fast with fetching a set but now ends up last.

Individual fetches without change tracking (100 individual fetches, 10 runs)

This benchmark fetches non-change tracked, read-only elements (see above) with the same setup as with the change tracked equivalent: it fetches 100 elements, one element at a time. 

Framework, without change-tracking, individual fetches individual fetch avg. (ms)
Dapper 0.58
Oak.DynamicDb using dynamic Dto class 0.62
ServiceStack OrmLite v4.0.9.0 (v4.0.9.0) 0.67
Handcoded materializer using DbDataReader 0.76
PetaPoco Fast v4.0.3 0.99
LLBLGen Pro v4.1.0.0 (v4.1.14.0117), typed view 1.55
Entity Framework v6.0.0.0 (v6.0.21211.0) 2.19
Linq to Sql v4.0.0.0 (v4.0.30319.18408) 2.54
PetaPoco v4.0.3  3.76

Here we see something interesting: some Micro-ORMs are faster than the hand-written materializer. The overhead in the hand-written materializer is done more efficiently by these frameworks it seems. Another thing of note is that PetaPoco, one of the fastest in the set fetch, is not very fast when it comes to fetching single elements. The same is true for Linq to Sql which was also not a great performer in the change tracked bench.

Now, I won't copy the same answers to the obvious questions from the previous post, so if you want to be Captain Obvious again and state something already answered there, please consult the list of remarks already addressed in the previous post.

If you want your framework added to the benchmark, please send a Pull Request. The framework must be used by developers out there.

Comments

# re: Fetch performance of various .NET ORM / Data-access frameworks, part 2

Wednesday, February 12, 2014 9:27 AM by A

My unfortunate experience with things from Telerik is that they only work well in default/mainline scenarios; when you need to do something particular, things get hairy really fast.

# re: Fetch performance of various .NET ORM / Data-access frameworks, part 2

Thursday, February 13, 2014 9:06 AM by Ricardo Peres

Why didn't you include NHibernate in all tests?

# re: Fetch performance of various .NET ORM / Data-access frameworks, part 2

Thursday, February 13, 2014 9:14 AM by FransBouma

@Ricardo Peres

You mean it's not included in the non-change tracking test? That's indeed an issue that's still open and we didn't have time to add the code for that. (github.com/.../9)

# re: Fetch performance of various .NET ORM / Data-access frameworks, part 2

Thursday, February 13, 2014 9:47 AM by Sherif

From my experience with EF6, I learned not to use value tracking for a large amount of records as this causes the performance to degrade drastically.

# re: Fetch performance of various .NET ORM / Data-access frameworks, part 2

Saturday, February 15, 2014 4:49 AM by Ricardo Peres

Hi, Frans!

I posted something on how to improve the performance of NHibernate: weblogs.asp.net/.../on-nhibernate-performance.aspx

# re: Fetch performance of various .NET ORM / Data-access frameworks, part 2

Monday, February 17, 2014 9:03 AM by Ricardo Peres

Pull request: github.com/.../16

# re: Fetch performance of various .NET ORM / Data-access frameworks, part 2

Monday, February 17, 2014 9:36 AM by FransBouma

@Ricardo, you closed it, so I assume it's not valid?

Jonny did some tests with your suggestions but it didn't give any performance gains at all.

# re: Fetch performance of various .NET ORM / Data-access frameworks, part 2

Monday, February 17, 2014 11:14 AM by Ricardo Peres

I closed it? Sorry about that!

That is bizarre, I do have my code which I can hand over to anyone interested in it.

# re: Fetch performance of various .NET ORM / Data-access frameworks, part 2

Monday, February 17, 2014 11:28 AM by FransBouma

I've reopened it. I'll look at it tomorrow. :)

# re: Fetch performance of various .NET ORM / Data-access frameworks, part 2

Monday, February 17, 2014 11:32 AM by Ricardo Peres

Frans:

Sorry, I spotted a problem in my solution. Maybe things aren't exactly as I said... I will let you know, ok?

# re: Fetch performance of various .NET ORM / Data-access frameworks, part 2

Monday, February 17, 2014 12:00 PM by FransBouma

@ricardo: Jonny's results with the exact same code you used: http://pastebin.com/iL4CMuXy

No large difference.

I'll close the pull request now, no worries :) If you have a better one, just send me a pull request and I'll have a look :)

The first time I posted about ORM performances, I did an extensive profiling of NHibernate. I posted a screenshot about it here: weblogs.asp.net/.../NHibernate_slow_4E4896E8.png

It really is slow, not because of some config setting, but it looks like it has some inefficiencies in identifyable areas. It shouldn't be hard for an NHibernate committer to find the code causing this and perhaps optimize it.