Data Access Performance Comparison in .NET

When writing data access code you are probably using some sort of ORM or data mapper to make your life easier while retrieving and persisting data. We all know that this does not come for free, ORMs allow you to move faster, but there is always a performance hit. I recently started reading about Dapper .NET, an open-source, lightweight, single-file object mapper that was developed for the stackoverflow.com site to execute high performance queries. After seeing its amazing performance results on their project page, I decided to write my owns performance tests to compare it to other ORMs (Entity Framework, NHibernate), and also to plain ADO.NET code.

For these tests, I am using the Chinook Database for SQL Server. I defined a simple repository interface and implemented it using different data access approaches. The interface implemented is:

public interface IRepository
{
 Artist GetArtistById(int id);
 IEnumerable<Song> GetSongsByArtist(string name);
}

The first method executes a simple query in a single table by the primary key. The second method executes a query in multiple tables using joins.

There are multiple implementations of this repository interface including plain ADO.NET code with DataReaders, Entity Framework, NHibernate and Dapper .NET. For the Entity Framework, there are four implementations: Linq To Entities, compiled Linq To Entities, native SQL query and stored procedures. For NHibernate, there are implementations with HQL query, native SQL query, and stored procedure.

All the code is available at https://github.com/lerocha/DotNetDataAccessPerformance.

Single Table Query by Primary Key

The first set of tests is to execute a simple query in a single table to get one record by the primary key. The SQL query is:

SELECT ArtistId, Name FROM Artist WHERE Artist.ArtistId=@id

The following table contains the results per multiple runs:

	1 run	10 runs	100 runs	500 runs	1000 runs
DataReaderNativeQuery	0.0	2.0	24.9	134.1	264.4
DataReaderStoredProcedure	0.0	2.2	26.3	133.8	266.4
DapperNativeQuery	0.1	2.5	28.0	141.5	285.6
DapperStoredProcedure	0.0	2.4	28.0	137.5	276.1
EntityFrameworkCompiledLinqQuery	2.2	22.5	207.2	1041.0	2099.8
EntityFrameworkLinqToEntities	5.5	25.2	267.7	1263.6	2522.5
EntityFrameworkNativeQuery	0.7	8.2	85.2	426.0	859.5
EntityFrameworkStoredProcedure	1.4	5.2	58.3	275.8	552.8
NHibernateHqlQuery	0.8	4.4	48.1	253.6	509.5
NHibernateHqlQueryStrongType	0.1	4.1	47.2	251.4	508.7
NHibernateNativeQuery	1.1	4.4	46.1	256.9	490.1
NHibernateStoredProcedure	0.0	5.2	57.2	294.0	589.8

The 1-run test is executed twice, the first execution result is not shown since I am only considering hot start. The following chart shows the results of 1,000 runs. As you can see, using Entity Framework with Linq To Entities (non-compiled) has the worst performance result (2,552.5 ms). It gets a little bit better if you use EF compiled Linq query (2,099.8), then improvements starts when using EF with native query (859.5 ms). EF using stored procedure and all NHibernate approaches are way better, all around 500 ms. And finally, the performance of Dapper is very close to the plain ADO.NET code, providing an level of abstraction with minimal performance impact.

Multiple Table Query with Joins

The second test executes joins in multiple tables and filters by a non-key column. The SQL query is:

 SELECT Album.Title as AlbumName, 
 Track.Name as SongName, Artist.Name as ArtistName
 FROM Artist
 INNER JOIN Album ON Album.ArtistId = Artist.ArtistId
 INNER JOIN Track ON Track.AlbumId = Album.AlbumId
 WHERE Artist.Name=@name

The following table contains the results per multiple runs:

	1 run	10 runs	100 runs	500 runs	1000 runs
DataReaderNativeQuery	0.0	5.9	63.0	321.8	644.2
DataReaderStoredProcedure	0.0	6.2	65.0	332.3	658.6
DapperNativeQuery	0.4	6.4	68.0	346.1	685.5
DapperStoredProcedure	0.1	6.7	67.2	342.3	679.7
EntityFrameworkCompiledLinqQuery	1.0	9.9	103.3	525.6	1071.0
EntityFrameworkLinqToEntities	5.6	58.2	539.5	2699.9	5398.3
EntityFrameworkNativeQuery	1.1	13.1	135.0	658.8	1328.3
EntityFrameworkStoredProcedure	1.1	9.7	99.0	489.1	983.7
NHibernateHqlQuery	3.4	35.6	359.5	1806.2	3647.1
NHibernateHqlQueryStrongType	6.8	68.3	681.5	3430.0	6841.4
NHibernateNativeQuery	1.1	10.6	103.2	517.7	998.7
NHibernateStoredProcedure	0.8	10.1	102.0	516.2	1033.8

The following chart shows the results of 1,000 runs. As you can see, the slowest implementation is NHibernate with strong type query, e.g. using NHibernate.IQuery.List(), it took 6,841 ms. EF with LinqToEntities (non-compiled) is a little bit faster (5,398 ms). Using NHibernate without strong type query, e.g. using NHibernate.IQuery.List() is significant faster (3,647 ms) than its strong type equivalent (6,841 ms). Here, we can see the performance improvement when using EF compiled Linq queries (1,071 ms) in comparison with EF non compiled Linq To Entities (5,398 ms). The EF compiled Linq query implementation showed performance equivalent to EF with stored procedures, and NHibernate with native query and stored procedure. And finally, the performance of Dapper (679-685 ms) is again very close to the plain ADO.NET code (644-658 ms), with minimal performance impact.

The performance improvements using Dapper in comparison with Entity Framework and NHibernate is very significant. For database reads that need a high performance execution, you might consider using Dapper to get the best performance with some good abstraction from plain ADO.NET code.

The source code of these tests is available at github and I will continue to work on it to incorporate any feedback and also add additional tests for adding, deleting and updating records. Stay tunned!