Comparing performance of data access libraries using StackExchange/Dapper benchmark

  • Article

One of the most important questions that you need to answer in your projects is what data access library you should use to access your data in SQL Server database. One of the benchmarks that you can use is StackExchange Dapper benchmark that checks how fast could different data access libraries fetch a single row from database.

In this post I will show you how to use StackExchange/Dapper performance benchmark to evaluate performance of different data access libraries. This is simple performance benchmark that executes 500 SQL queries that read a single row from Posts table and return results. Example of test for hand coded query implemented using the plain SqlCommand/DataReader is shown in the following code:

 var postCommand = new SqlCommand();
postCommand.Connection = connection;
postCommand.CommandText = @"select Id, [Text], [CreationDate], LastChangeDate, 
 Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id";
var idParam = postCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int);
using (var reader = postCommand.ExecuteReader())
{
 reader.Read();
 var post = new Post();
 /* populate fields in Post object */
}

The same query is implemented in different frameworks and test measures elapsed time for 500 iterations of this query.

Setting-up the test

To setup test, you can go to StackExchange/Dapper GitHub an download source code. Tests are created as C# solution (Dapper.sln). When you open this solution you can find Dapper.Tests project. You might need to change two things:

  1. Connection strings are hardcoded in Tests.cs file with values like "Server=(local)\SQL2014;Database=tempdb;User ID=sa;Password=Password12!". You might need to change this and put your connection info.
  2. Project is compiled using dotnet sdk 1.0.0-preview2-003121, so you might get compilation errors if you don't have a matching framework. I have removed line: "sdk": { "version": "1.0.0-preview2-003121" } from global.json to fix this.

Now you will be able to build project and run tests.

Executing tests

To start tests you need to start Dapper.Tests project and results will be shown in console. I have executed test with the following results:

 Running 500 iterations that load up a post entity

Running...
hand coded took 122ms
Mapper QueryFirstOrDefault took 125ms
Dynamic Mapper Query (buffered) took 130ms
Dynamic Massive ORM Query took 140ms
Dapper.Contrib took 147ms
DataTable via IDataReader.GetValues took 157ms
Mapper Query (buffered) took 160ms
Linq 2 SQL Compiled took 168ms
Mapper Query (non-buffered) took 179ms
Dynamic Mapper Query (non-buffered) took 195ms
Dynamic Mapper QueryQueryFirstOrDefault took 198ms
Entity framework SqlQuery took 202ms
Linq 2 SQL ExecuteQuery took 302ms
Entity framework No Tracking took 365ms
Entity framework took 378ms
Simple.Data took 767ms
Linq 2 SQL took 905ms

As expected, hand coded data access with data readers is the faster approach. Interesting fact is that Dapper library adds minimal overhead. It is slightly slower than hand coded data access. Compiled Linq 2 SQL also shows good results with 40% overhead compared to hand coded data access.

Entity Framework with Database.SqlQuery<Post>("SQL QUERY") adds ~65% overhead, so this might be preferred way to execute queries if performance of EF are important for you.

Entity Framework with Linq, e.g. entityContext.Posts.First(p => p.Id == id) is around x3 slower that handcoded data reader.

Conclusion

This test compares performance of different data libraries with the faster handcoded approach. Using this test you can see what is the overhead of different libraries that are built on top of standard ADO.NET.

Dapper ORM is simple and interesting framework that has enables you to access your data with acceptable performance overhead. I have checked the test code and there is nothing specific to Dapper library that might bring false positive results.

If you are using Entity Framework you should try to use raw SQL queries if performance are important for you. Hopefully in the future we will see some performance improvements in EF Linq that will be closer to EF Query method.