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
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!
