Adding LLBLGen to the Dapper performance benchmark test project

The Dapper project on GitHub has a performance/benchmark test project that compares a number of ORMs and data access strategies. While the project only tests a very simple fetch algorithm, it’s a good starter indicator for performance. I thought I would add one of my favorite ORMs (LLBLGen) to the mix, and see how it performs. Code samples are included in this post. The Dapper project hosted on GitHub at https://github.com/SamSaffron/dapper-dot-net has a performance/benchmark test project that compares a number of ORMs and data access strategies. While the project only tests a very simple fetch algorithm, it’s a good starter indicator for performance. I thought I would add one of my favorite ORMs (LLBLGen) to the mix, and see how it performs. > Post update: 5/3/2013 > > LLBLGen has recently published it’s V4 release. In addition to this post, you may also be interested in these LLBLGen Pro v4 benchmarks.

The Tests

Here’s the code I added to the PerformanceTests.cs class file. Use this code, in conjunction with the download further down to perform the tests yourself if you’re familiar with LLBLGen. Here are the LLBLGen Adapter Tests:

//LLBLGen Adapter
var adapter1 = new DataAccessAdapter(Program.connectionString, true, CatalogNameUsage.Clear, null);
adapter1.OpenConnection();
tests.Add(id => adapter1.FetchEntity(new PostEntity(id)), "LLBLGen Adapter (FetchEntity w/ KeepConnectionOpen)");

var adapter2 = new DataAccessAdapter(Program.connectionString, false, CatalogNameUsage.Clear, null);
adapter2.OpenConnection();
tests.Add(id => adapter2.FetchEntity(new PostEntity(id)), "LLBLGen Adapter (FetchEntity)");

var adapter3 = new DataAccessAdapter(Program.connectionString, true, CatalogNameUsage.Clear, null);
adapter3.OpenConnection();
tests.Add(id =>
  {
      var bucket = new RelationPredicateBucket();
      bucket.PredicateExpression.Add(PostFields.Id == id);
      var entities = new EntityCollection();
      adapter3.FetchEntityCollection(entities, bucket);
      entities.First();
  }, "LLBLGen Adapter (Predicate w/ KeepConnectionOpen)");

var adapter4 = new DataAccessAdapter(Program.connectionString, false, CatalogNameUsage.Clear, null);
adapter4.OpenConnection();
tests.Add(id =>
  {
      var bucket = new RelationPredicateBucket();
      bucket.PredicateExpression.Add(PostFields.Id == id);
      var entities = new EntityCollection();
      adapter4.FetchEntityCollection(entities, bucket);
      entities.First();
  }, "LLBLGen Adapter (Predicate)");

Here are the LLBLGen Self-Servicing Tests:

//LLBLGen SelfServicing
CommonDaoBase.ActualConnectionString = Program.connectionString;
tests.Add(id => (new LLBLGenSS.EntityClasses.PostEntity()).FetchUsingPK(id), "LLBLGen SelfServicing (UC Fetch)");

tests.Add(id => new LLBLGenSS.EntityClasses.PostEntity(id, (IPrefetchPath)null), "LLBLGen SelfServicing (PK in constructor)");

tests.Add(id =>
  {
      var entities = new LLBLGenSS.CollectionClasses.PostCollection();
      entities.GetMulti(LLBLGenSS.HelperClasses.PostFields.Id == id);
      entities.First();
  }, "LLBLGen SelfServicing (Predicate)");
var transactionManager = new LLBLGenSS.HelperClasses.Transaction(IsolationLevel.ReadUncommitted, "Test", Program.connectionString);
tests.Add(id =>
  {
      var entity = new LLBLGenSS.EntityClasses.PostEntity();
      transactionManager.Add(entity);
      entity.FetchUsingPK(id);
  }, "LLBLGen SelfServicing (UC Fetch in Transaction)");

Now let’s see the results.

The Results

In plain text format:

Strategy	Time
Dynamic Mapper Query (buffered)	57ms
Mapper Query (non-buffered)	58ms
Mapper Query (buffered)	59ms
Dynamic Mapper Query (non-buffered)	59ms
hand coded	59ms
PetaPoco (Fast)	63ms
Dapper.Cotrib	64ms
OrmLite QueryById	64ms
PetaPoco (Normal)	67ms
Dynamic Massive ORM Query	69ms
BLToolkit	95ms
Simple.Data	98ms
Linq 2 SQL Compiled	106ms
LLBLGen Adapter (FetchEntity w/ KeepConnectionOpen)	121ms
SubSonic Coding Horror	123ms
NHibernate Session.Get	124ms
LLBLGen SelfServicing (UC Fetch in Transaction)	126ms
LLBLGen Adapter (Predicate w/ KeepConnectionOpen)	127ms
NHibernate SQL	128ms
Entity framework CompiledQuery	135ms
NHibernate HQL	141ms
LLBLGen Adapter (FetchEntity)	145ms
LLBLGen SelfServicing (UC Fetch)	148ms
LLBLGen SelfServicing (PK in constructor)	150ms
LLBLGen Adapter (Predicate)	155ms
LLBLGen SelfServicing (Predicate)	164ms
NHibernate Criteria	175ms
Soma	186ms
Linq 2 SQL ExecuteQuery	240ms
Linq 2 SQL	737ms
NHibernate LINQ	769ms
Entity framework ESQL	812ms
Entity framework ExecuteStoreQuery	829ms
Entity framework	1063ms
Entity framework No Tracking	1065ms
SubSonic ActiveRecord.SingleOrDefault	4874ms

Download

To run the LLBLGen test, just include the 2 folders in the attached zip file into the Dapper project, and reference the LLBLGen assemblies

Project:

References:

[Download Code](/funcoding/files/2012/02/LLBL.zip)

Conclusion

While LLBLGen is not a Micro-ORM, the LLBLGen Adapter and SelfServicing runtime frameworks rank up there at the top performance-wise. I’ve worked with Entity Framework extensively in the past few months, including Code-First, even though that doesn’t make me an expert, but none-the-less, LLBLGen is more attractive now than ever before. We’ll see how EF 5.0 turns out. Meanwhile, NHibernate, PetaPoco, Dapper, and LLBLGen all look like good options.

Separate study (updated on 4/20/2012)

I found the codebase I used a while back to compare basic CRUD operations between EF 4.1, Dapper and LLBGen 3.1. I took a quick stab at refactoring it a little with newer libraries and here are the findings. The results are in milliseconds, and are averages taken over 5 iterations of 1000, 2000, and 5000 user records. Code is provided as a download. I make a distinction in my tests between BATCH and ATOMIC transactions. A BATCH transaction is a transaction over the entire span of records (start_transaction foreach { do_record_update } commit_transaction); whereas, an ATOMIC transaction is a transaction on each individual record (foreach {  start_transaction do_record_update commit_transaction }).

All frameworks “delete”, and “insert” about the same. Dapper clearly outperforms both LLBGen and EF in batch reading data out of a database (although, all frameworks retrieve over 5000 records in less than 90ms in my tests, which is probably good enough for most applications), unless you’re running a site like StackOverflow, in which case, you might as well switch to a search engine instead. LLBLGen is a near 2nd, EF takes 3rd place (with some concerns in the area of “atomic reads” and “updates”). Of course, I make assumptions in my tests, and the database in these tests is not indexed, so this isn’t the final say on the matter, take it as you will . [Separate Study Code](/funcoding/files/2012/04/misc_orm_perf_tests.zip)