Sunday, January 29, 2012

MicroORM performance investigations

Hi All.

I would like to share some investigations on few MicroORM performance.
These investigations are inspired mostly by dapper-dot-net extensive performance.
I was choosing a fast ORM for my load-critical application. Dapper's select extreme performance was demonstrated. But what about inserts? Dapper do not explicitly support inserts, you can write a SQL query by yourself, but with the risk of getting column name or parameter wrong. After some browsing I found Dapper-Extensions and PetaPoco that seem to have an ability to insert/update an entity without specifying all columns explicitly. I also wanted to know what Entity Framework 4.2 performance is.



My test was iterating over fixed number of iterations for SELECT and INSERT operations with different ORMs (Dapper, DapperExtensions, PetaPoco, EF4.2).
Here are my results.

UPDATE: Thanks to Sam I've removed timing of first iteration, because ORMs do caching at this point. Due to this I've removed small (10) iterations as not reflecting the situation and updated cnt=1000 iteration results.

If number of iterations is small (cnt = 10).
Dapper select 28,3203 ms
EF select 761,7188 ms
Dapper explicit query insert 29,2968 ms (explicit SQL query written with parameters)
DapperExtensions insert 57,6172 ms
PetaPoco insert 17,5781 ms
EF insert 56,6406 ms


Some comments:
Dapper select is ultra-fast. Use it where possible. Dapper extensions INSERTs are not so fast. They are like Entity Framework. So in this case I would choose PetaPoco.

Number of iterations is large (cnt = 1000).
Dapper select 1198,2422 ms
EF select 2038,086 ms
Dapper explicit query insert 249,0235 ms (explicit SQL query written with parameters)
DapperExtensions insert 500 ms
Dapper explicit update 230,4687 ms
PetaPoco insert 274,4141 ms
EF insert 4848,6328 ms

Dapper select 89,8437 ms
EF select 204,1015 ms
Dapper explicit query insert 205,0781 ms
DapperExtensions insert 789,0625 ms
PetaPoco insert 286,1328 ms
EF insert 5015,625 ms

Some comments:
EF select is not far from Dapper (2 times slower).  Dapper extensions insert exhibits good performance comparing to EF (10 times faster), PetaPoco is 2 times faster than Dapper Extensions.

Overall conclusion:
It seems that EF adds a huge overhead for small amount of operations. For huge amoung of operations that overhead for SELECT is not so notable, but frustrating for INSERT.

UPDATE: Conclusions have not been changed after the update.
So what I decided finally:
Use dapper for SELECTs.
Use PetaPoco for INSERTs.

Explicit query writing is not my choice because it is very annoying. Although it is fast.

Further reading:
http://samsaffron.com/archive/2012/01/16/that-annoying-insert-problem-getting-data-into-the-db-using-dapper
http://www.toptensoftware.com/petapoco/

In my code I used slightly modified PetaPoco and DapperExtensions. These changes does not affect performance at all.

Feel free to share your opinions.
Finally my code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using Dapper;
using DapperExtensions;
using System.Data.Entity;
using System.Transactions;
namespace dapper_test
{
 public class Group
 {
  public int Id { get; set; }
  public string Name { get; set; }
 }
 public class User
 {
  public int Id { get; set; }
  public string Name { get; set; }
  public int GroupId { get; set; }
  public Group Group { get; set; }
 }
 public class TestContext : DbContext
 {
  public DbSet User { get; set; }
  public DbSet Group { get; set; }
  public TestContext(string conn)
   : base(conn)
  {
  }
  public TestContext(DbConnection conn)
   : base(conn, false)
  {
  }
 }
 class Program
 {
  static void Main(string[] args)
  {
   var connStr = @"Data Source=localhost\sqlexpress;Initial Catalog=Test;Integrated security=True;";
   var conn = new SqlConnection(connStr);
   conn.Open();
   conn.Execute("DELETE [Users] WHERE Id <> 2");
   int cnt = 1000;
   // warmup
   conn.Query("select * FROM [Users] JOIN [Groups] ON [Groups].Id = [Users].GroupId", (g, u) => { u.Group = g; return u; });
   var start = DateTime.Now;
   for (int i = 0; i < cnt; i++)
   {
    var users = conn.Query("select * FROM [Users] JOIN [Groups] ON [Groups].Id = [Users].GroupId", (g, u) => { u.Group = g; return u; });
   }
   Console.WriteLine("Dapper select {0} ms", DateTime.Now.Subtract(start).TotalMilliseconds);
   // warmup
   var ctx = new TestContext(connStr);
   ctx.User.Include("Group").Load();
   ctx.User.Local.ToArray();
   start = DateTime.Now;
   for (int i = 0; i < cnt; i++)
   {
    ctx.User.Include("Group").Load();
    var users = ctx.User.Local.ToArray();
   }
   Console.WriteLine("EF select {0} ms", DateTime.Now.Subtract(start).TotalMilliseconds);
   // Dapper insert
   // warmup
   conn.Query("INSERT [Users] (Name, GroupId) VALUES (@Name, @GroupId) SELECT CAST(scope_identity() as INT)", new User { Name = "TestD", GroupId = 1 });
   start = DateTime.Now;
   var dapperIds = new List(cnt);
   //var tran = conn.BeginTransaction();
   for (int i = 0; i < cnt; i++)
   {
    var user = new User { Name = "TestD", GroupId = 1 };
    user.Id = conn.Query("INSERT [Users] (Name, GroupId) VALUES (@Name, @GroupId) SELECT CAST(scope_identity() as INT)", user/*, tran*/).First();
    dapperIds.Add(user.Id);
   }
   //tran.Rollback();
   Console.WriteLine("Dapper explicit query insert {0} ms", DateTime.Now.Subtract(start).TotalMilliseconds);
 
   // Dapper extensions insert
   // warmup
   conn.Insert(new User { Name = "TestD", GroupId = 1 });
   start = DateTime.Now;
   dapperIds = new List(cnt);
   //var tran = conn.BeginTransaction();
   for (int i = 0; i < cnt; i++)
   {
    var user = new User { Name = "TestD", GroupId = 1 };
    conn.Insert(user);
    dapperIds.Add(user.Id);
   }
   //tran.Rollback();
   Console.WriteLine("DapperExtensions insert {0} ms", DateTime.Now.Subtract(start).TotalMilliseconds);
   // PetaPoco
   // warmup
   var db = new PetaPoco.Database(conn);
   db.Insert("Users", "ID", new User { Name = "TestP", GroupId = 1 });
   start = DateTime.Now;
   var petaIds = new List();
   //db.BeginTransaction();
   for (int i = 0; i < cnt; i++)
   {
    var user = new User { Name = "TestP", GroupId = 1 };
    db.Insert("Users", "ID", user);
    //db.Insert(user);
    petaIds.Add(user.Id);
   }
   //db.AbortTransaction();
   Console.WriteLine("PetaPoco insert {0} ms", DateTime.Now.Subtract(start).TotalMilliseconds);
   // EF
   ctx = new TestContext(connStr);
   // warmup
   ctx.User.Add(new User { Name = "TestE", GroupId = 1 });
   start = DateTime.Now;
   for (int i = 0; i < cnt; i++)
   {
    var user = new User { Name = "TestE", GroupId = 1 };
    ctx.User.Add(user);
    ctx.SaveChanges();
   }
   Console.WriteLine("EF insert {0} ms", DateTime.Now.Subtract(start).TotalMilliseconds);
  }
 }
}

2 comments:

  1. timing the first iteration for any orm is a big mistake, they perform caching at that point. there is no real world case where you need to optimise for 10 iterations within the app life cycle. also .. see tests in dapper, the best thing to do is randomise the iteration.

    ReplyDelete
    Replies
    1. I've updated the post and removed timing of the first iteration. Results still haven't been changed much.

      Regarding randomization - I am not sure that it will influence the results. The goal of the test is to measure time spent in ORM for mapping operations. If queries are cached on SQL Server side - well, this is fine for us.

      Delete