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!
6 comments:
Hello,
Thank you for sharing these statistics.
I believe we can do some optimization for the nHibernate side.
I put some changes along with commentaries what do I mean here:
https://github.com/atanashristov/DotNetDataAccessPerformance/blob/master/DotNetDataAccessPerformance/Repositories/NHibernateHqlQueryStrongTypeRepository.cs
I'll be glad if you consider to test with these versions I'm suggesting.
Thank you!
Atanas
Thank you for sharing these project and the numbers!
I believe I have some optimization for a nHibernate selection, I put some code together with commentaries at:
https://github.com/atanashristov/DotNetDataAccessPerformance/blob/master/DotNetDataAccessPerformance/Repositories/NHibernateHqlQueryStrongTypeRepository.cs
I'll be glad if you consider to take a look and give a try if these versions will perform better than the original version.
Thank you!
Atanas
Where is Entity Framework Code First?
Nice work. I would also venture to say that LLBLGen would be in the ballpark of the EF compiled query / NHibernate native queries benchmarks (based on my findings http://tinyurl.com/6wgxcyp).
I still found some flaws in the NHibernate HQL implementation:
- Queries are not parameterized, but have parameter values concatenated into the HQL/SQL string (while Dapper, for example, has correct use of parameter placeholders). This will cause some more database load (no DB query plan cache usage) and risk of SQL injection in "real life". To your information: NHibernate uses an Oracle-style colon : as parameter prefix, instead of the native MS SQL Server @ prefix. The Criteria query added by ahristov (GetSongsByArtist2) will also fix this.
- No projection (select list) in join query, will load all columns from the related tables. GetSongsByArtist3 from ahristov also fixes this, but unfortunately still has a concatenated HQL/SQL parameter.
I must thank you for the efforts you've put
in penning this site. I'm hoping to view the same high-grade blog posts from you later on as
well. In fact, your creative writing abilities has encouraged me to get my very own website now ;)
Feel free to visit my blog post; hack clash of clans
Post a Comment