fransbouma / rawdataaccessbencher Goto Github PK

Bench code which tests entity materialization speed of various .NET data access / ORM implementations

License: MIT License

C# 100.00%

rawdataaccessbencher's Introduction

RawDataAccessBencher

Bench code which tests entity materialization speed of various .NET data access / ORM implementations. The tests focus solely on entity / object materialization and therefore don't do any fancy queries, graph fetches or other nice things which one expects from ORMs. It's not a scientific benchmark system, but an indication to see which framework is fast and which is slow compared to each other. See for more details about what this test is not: http://weblogs.asp.net/fbouma/archive/2014/02/13/re-create-benchmarks-and-results-that-have-value.aspx

Results

The results of various runs of the benchmark are available as files in the repository, please see the Results folder, which contains per run a .txt file with the results measured.

Requirements

.NET 4.8.0 and .NET 5.0, SQL Server with AdventureWorks example database (2008 version, available on Github). See below how to install/configure it. The mappings supplied are for the 2008 version of the example database.

How to attach / install the database

Install SQL Server 2008 or higher and download the 2008 version of the database from the link above. Unpacking the zip will give you two files. Copy the two files in the DATA folder of your sql server installation and attach the mdf file in SQL Server Management Studio.

Important: it's important you attach the database as 'AdventureWorks', as the code expects it to be called that way. If you leave the name as-is, the Catalog name has '_2008' as suffix. LLBLGen Pro has the catalog name in the persistence information (so it can have multiple catalogs in one model) and to make it work you have to enable the catalog name overwriting defined in the app.config file in the rawbencher project. It's commented so it's straight forward. If you attached the database as AdventureWorks, you don't have to do anything.

How to run the benchmarks

Please run the benchmarks on a DB accessed over a network to avoid having the DB consume performance of the CPU / memory which thus doesn't give a real-life scenario overview of the real fetch speed of the used Data-access / ORM framework. The entity SalesOrderHeader was chosen as it has more than a couple of fields, a variety of types, many rows in the table and several relationships with other entities which could or could not affect the ORM's internal performance.

If you want to export the results to a file directly, run the RawBencher.exe from the command line and specify /a at the end so it will exit immediately when it's done. Example: RawBencher.exe /a > results.txt

'Framework X isn't there anymore, why?'

CodeFluent Entities was part of the code base, but has been removed, as they kept the connection open during individual fetches, which gave them an unfair advantage.
In previous versions the Telerik Open Access ORM was present, however compiling the code is impossible without the 'enhancer' tool of Telerik present on the system. To make it easier for people to download and compile the code, we therefore removed the Telerik bencher. If you want them, browse to a previous commit and pull the bencher classes and model from there.

Remarks about the benchmark results

I've included some optimizations for Entity Framework Core, which can be used in production but one should be careful. I fully understand these optimizations could be seen as 'ways to win in a benchmark', as it's recommended only to enable these features if you're sure you're not running into the side effects of it. The main reason I included them is that there's already corner cutting going on by some frameworks: relying on the datareader.GetSchemaTable for which columns could contain NULL values.

The way this works is that if you can determine a result set column which according to the mappings could have NULL values but in this particular set doesn't contain NULL values, you can skip the NULL check on each value for that column in each row, which can result in faster set fetches. The downside is that this setup can fail when you fetch a query from a SQL Server View which is formed by a query on tables which have been changed. Because of this my own framework, LLBLGen Pro, doesn't implement this optimization, but some others do.

As with all benchmarks, you have to do your own testing on your hardware with your usage profile to see if the framework you've chosen is indeed fast enough. The tests in this project give a good overview of the performance characteristics of the various frameworks relative to each other but it's not said in your project they perform the same.

Remarks per used framework

NHibernate uses .hbm mappings, as this is of no relevance to the fetch speed and it avoids a dependency on FluentNHibernate.

In the Entity Framework code, Foreign key fields are present in the code base, as other frameworks fetch them too. This makes Entity Framework become slow in 6.0.2 and earlier. With 6.1 this is partially fixed with 20%-30% faster code (see: http://entityframework.codeplex.com/workitem/1829) however it's still slower most frameworks with FK fields in Entity Framework v6.1. Without Foreign key fields present, Entity Framework takes roughly half the performance of 6.0.2 with FK fields present and ~70% of 6.1.

Including data-table fetches might look like an apple/oranges comparison, but so is Full ORM vs. Micro 'ORM', as a Micro ORM like Dapper has less things to worry about than, say NHibernate or LLBLGen Pro. The inclusion of these frameworks is done to show what can be achieved if there's little overhead between the DbDataReader and the materialized object. The closer an ORM gets to these lower-level object materializers, the better it is in fetching data with inclusion of the extra features if has to offer to the developer and the application it is used in.

Disclaimer

I wrote LLBLGen Pro, though I tried to keep this benchmark as honest and open as possible.

rawdataaccessbencher's People

Contributors

Stargazers

Watchers

rawdataaccessbencher's Issues

Add for every framework an individual entity fetch test

It currently only has large tabular test for all frameworks. Additional tests should be added so each framework is also shown with individual entity fetch tests, e.g. fetch the first 1000 salesorderheaders individually, aggregate the time.

Add catalog name overwriting directives to app.config file

They're currently missing. They can be useful if the user has a differently named adventureworks catalog.

Make the single element fetch do a random fetch, not sequential

It currently reads elements sequentially for the single element fetches, not using random keys. It might make some (not much) difference, though it likely won't be a significant delimiter as each framework has to deal with the slower fetch on the DB, materialization speed will be equal.

Code isn't compatible with example dbs from MS at codeplex

The example Adventureworks DBs from Microsoft at codeplex (https://msftdbprodsamples.codeplex.com/releases/view/93587) are not compatible with the code as the all lack a table (Contact) and have slightly different types in some other tables. To make more people able to work with the code, the benchmark should be converted to the schema available on the codeplex site, e.g. the 2008 schema (which is equal to the 2012 one)

The code in source control at codeplex does have the right schema / data, but it's not easy to import it (csv files).

EF Core 2.0 benchmark

Is missing

Microsoft.SqlServer.Types x64 DLLs are missing after restore from nuget.

Under SqlServerTypes, I find the x86 DLLs but not the x64 versions.

Work-around:

Reinstalling the NuGet package Microsoft.SqlServer.Types, then reverting any changes, seems to restore the missing files.

Add EF Core NoChangeTracking with CompiledQuery Bencher

Add a new bencher for EF Core NonChange tracking with CompiledQuery to test what performance improvement CompileQuery will give.

EF Core 6.0 benchmark

It would be interesting to see how ef core 6. 0 does in these benchmarks, please.

Can we inject our ORM for this bencher? Or, what are the criteria?

Hi Frans, I do not know how to contact you, so I hope it is okay to create an issue with correct labels.

We spent too much time developing a lightweight ORM recently and is using this bencher to test the performance against the other.

Thinking if, what's the criteria for our ORM to be a part of this bencher?

Do we need to be popular or have more users first?

For now, I created a blog how we made our library works as fast-as-possible.
Link: http://codesdirectory.blogspot.com/2018/09/repodb-net-lightweight-orm-library.html

Here is the cloned repository with our ORM.
Link: https://github.com/RepoDb/RawDataAccessBencher

RawBencher doesn't compile when new clone of repository

RawBencher.csproj does not compile a new clone of repository as there is checks for packages that no longer exist in packages folder.

In my new clone of repository and using VS2022 i only have the folders in packages:

25.07.2022  11:22    <DIR>          .
25.07.2022  08:42    <DIR>          ..
25.07.2022  08:42    <DIR>          EntityFramework.6.4.4
25.07.2022  08:42    <DIR>          linq2db.4.1.1
25.07.2022  08:42    <DIR>          Microsoft.Bcl.AsyncInterfaces.6.0.0
25.07.2022  08:42    <DIR>          System.Runtime.CompilerServices.Unsafe.6.0.0
25.07.2022  08:42    <DIR>          System.Threading.Tasks.Extensions.4.5.4`

but RawBencher.csproj has these checks for folders:

<Target Name="EnsureNuGetPackageBuildImports" BeforeTargets="PrepareForBuild">
  <PropertyGroup>
    <ErrorText>This project references NuGet package(s) that are missing on this computer. Use NuGet Package Restore to download them.  For more information, see http://go.microsoft.com/fwlink/?LinkID=322105. The missing file is {0}.</ErrorText>
  </PropertyGroup>
  <Error Condition="!Exists('..\packages\NETStandard.Library.2.0.3\build\netstandard2.0\NETStandard.Library.targets')" Text="$([System.String]::Format('$(ErrorText)', '..\packages\NETStandard.Library.2.0.3\build\netstandard2.0\NETStandard.Library.targets'))" />
  <Error Condition="!Exists('..\packages\EntityFramework.6.4.4\build\EntityFramework.props')" Text="$([System.String]::Format('$(ErrorText)', '..\packages\EntityFramework.6.4.4\build\EntityFramework.props'))" />
  <Error Condition="!Exists('..\packages\EntityFramework.6.4.4\build\EntityFramework.targets')" Text="$([System.String]::Format('$(ErrorText)', '..\packages\EntityFramework.6.4.4\build\EntityFramework.targets'))" />
  <error condition="!exists('..\packages\microsoft.codeanalysis.analyzers.3.3.0\build\microsoft.codeanalysis.analyzers.props')" text="$([system.string]::format('$(errortext)', '..\packages\microsoft.codeanalysis.analyzers.3.3.0\build\microsoft.codeanalysis.analyzers.props'))" />
  <error condition="!exists('..\packages\microsoft.codeanalysis.analyzers.3.3.0\build\microsoft.codeanalysis.analyzers.targets')" text="$([system.string]::format('$(errortext)', '..\packages\microsoft.codeanalysis.analyzers.3.3.0\build\microsoft.codeanalysis.analyzers.targets'))" />
  <error condition="!exists('..\packages\microsoft.data.sqlclient.sni.2.1.0\build\net46\microsoft.data.sqlclient.sni.targets')" text="$([system.string]::format('$(errortext)', '..\packages\microsoft.data.sqlclient.sni.2.1.0\build\net46\microsoft.data.sqlclient.sni.targets'))" />
</Target>

Stddev on individual fetches is wrong

It's calculated over the time 100 fetches was taken, but is reported as the stddev of an individual fetch.

Update for EF Core 1.0

Would be great if you could update measurements for EF Core 1.0

Warm-up fetch

Before running the set fetch you perform a warm-up grab that you discard. Any reason why you don't also discard the first single element? From what I see, the first single element request weighs more than the subsequent requests -- looking at Dapper -- which skews the results a bit.

EF Core should use a PooledDbContextFactory

Include enumeration time in total time

It currently doesn't include enumeration time in total time. This is not really fair, as some frameworks defer actions to enumeration time, making them appear faster than they really are.

A clean clone compilation errors

I was trying to run this project to investigate a poor performance of some ORM. But the project does not compile. There are about 6K errors and 7 warnings.

As I understand there are some issues with the reference packages.

Use EF Core 7 and latest RepoDb

EF Core - DbContext should have EnableThreadSafetyChecks(false)

As described on EF Core Advanced Performance Topics the DbContext could/should also be configured with EnableThreadSafetyChecks(false).

public EntityFrameworkCoreNoChangeTrackingBencher(string connectionString)
	: base(e => e.SalesOrderId, usesChangeTracking: false, usesCaching: false)
{
	var options = new DbContextOptionsBuilder<AWDataContext>()
		.UseSqlServer(connectionString)
		.EnableThreadSafetyChecks(false)
		.Options;

	pooledDbContextFactory = new PooledDbContextFactory<AWDataContext>(options);
}

I made a copy of the EntityFrameworkCoreNoChangeTrackingBencher and created a new test where the only change is EnableTreadSafetyChecks(false) that is show here with NTSC and running on my dev computer gives this result:

Results per framework. Values are given as: 'mean (standard deviation)'

Non-change tracking fetches, set fetches (10 runs), no caching

Entity Framework Core NTSC v6.0.7.0 (v6.0.722.31501) : 70,29ms (0,67ms) Enum: 0,93ms (0,10ms)
Entity Framework Core v6.0.7.0 (v6.0.722.31501) : 80,37ms (6,19ms) Enum: 1,02ms (0,09ms)

Memory usage, per iteration

Entity Framework Core NTSC v6.0.7.0 (v6.0.722.31501) : 16 673 KB (17 073 744 bytes)
Entity Framework Core v6.0.7.0 (v6.0.722.31501) : 16 673 KB (17 073 744 bytes)

Non-change tracking individual fetches (100 elements, 10 runs), no caching

Entity Framework Core NTSC v6.0.7.0 (v6.0.722.31501) : 0,16ms (0,00ms) per individual fetch
Entity Framework Core v6.0.7.0 (v6.0.722.31501) : 0,19ms (0,01ms) per individual fetch

Memory usage, per individual element

Entity Framework Core NTSC v6.0.7.0 (v6.0.722.31501) : 17 KB (17 904 bytes)
Entity Framework Core v6.0.7.0 (v6.0.722.31501) : 17 KB (17 904 bytes)

That is about 15% lower response time.

Add a 'no change tracking' test for NHibernate.

It has the feature but it's currently not included.

Add a small results set query test

Currently, the only "small results set" test in the benchmark seems to be a single entity load, which is a somewhat special case of query, quite trivial. Other tests do load quite big results set.

Performances on big results set are interesting in a number of cases. But for a lot of applications, it is also very important to have good performances on queries which are not just a single load by key and have small result sets (less than hundred entities, like just ten or twenty). Such queries could represent the majority of an application work, and being able to execute many of them in a small time frame is then critical for the application scalability.

Could a small results set query test be added? It would define such a query, run it many times and measure the overall execution time (of course without using a second level cache).

We will still miss another important aspect, how do the data access layers fare when they run queries in parallel? But I am not sure a parallel test would be very significant, because its behavior may be too tightly bound to the hardware and may be too sensitive to other random background tasks of the OS.

Update Benchers for EF6, EF Core and PetaPoco

Update .NET project s to .NET 4.8 and NET Core projects to .NET5

Update benchers for EF6 and EF Core so that:

EF6 benchers run in both .NET 4.8 and NET5
Update EF Core benchers to use EF Core 3.1 in .NET4.8 and EF Core 5 in .NET5
Update EF Core DTO bencher to run in Both .NET4.8 and .NET5
Update PetaPoco benchers to use PetaPoco.Compiled v6