Entity Framework Core vs Dapper Performance in 2023
Vložit
- čas přidán 23. 03. 2023
- Check out my courses: dometrain.com
Become a Patreon and get source code access: / nickchapsas
Hello everybody I'm Nick and in this video I will compare Entity Framework Core's and Dapper's performance to see which one is faster and more memory efficient and by how much. The results were actually pretty interesting.
Workshops: bit.ly/nickworkshops
Don't forget to comment, like and subscribe :)
Social Media:
Follow me on GitHub: bit.ly/ChapsasGitHub
Follow me on Twitter: bit.ly/ChapsasTwitter
Connect on LinkedIn: bit.ly/ChapsasLinkedIn
Keep coding merch: keepcoding.shop
#csharp #dotnet
For those asking about Compiled Queries, I originally had them in my notes but I totally forgot to record them. Sorry for that.
That's what getting a single item by the PK using compiled queries would look like:
| EF_Single | 34.855 us | 6.8 KB |
| EF_Single_Compiled | 10.325 us | 2.75 KB |
| Dapper_GetById | 7.302 us | 2.23 KB |
That's what filtering would look like with compiled queries:
| EF_Filter | 32.77 us | 7.84 KB |
| EF_Filter_Compiled | 12.98 us | 4.24 KB |
| Dapper_Filter | 12.60 us | 3.84 KB |
As you can tell, it's way closer to Dapper in speed and memory.
Do keep in mind that they are NOT free or a silver bullet. Again, sorry for not adding that part in the video. I will be making a dedicated video on them to address this.
Nice👍
so nick i want ask something i hope u read this, do you recommend dapper over ef?
It totally depends, that was his point
@@xelesarc1680 EF is great for not worrying about SQL for basic operations you might want to do. Dapper is awesome if you need performance or if you need highly specific GET queries where writing the SQL is actually easier than dealing with EF.
It's not at all strange to have one technology for querying data and a different technology for command endpoints.
Great video as always. So am I correct in assuming that most of the overhead in EF comes from translating the Linq expression tree to SQL and that using FromSql or FromSqlInterpolated would perform about as well as Dapper?
I would love to see a video about why AsNoTracking is slower and also less memory efficient.
yes i would like to see that too, iused all read query with as no tracking i think its more faster then i see this video i want change all my read to dapper
I was shocked to see this. I always used this as blogs everywhere show improvement with it.
same here, thanks for the videos nick
I have not seen this in practice. That would blow my mind. Ive shaved 40% off queries by just switching it off for some calls
Are you sure this is correct? AsNoTracking has always been more performant and memory efficient for me
Hi Nick
It would be very exciting to know why AsNoTracking is slower and also less memory efficient....
Great videos, thanks for that!
+
+
+
+
Me too
You should have also compared EFcore raw sql APIs for queries as well. I'm also wondering how far could you push it using compiled queries, compiled models, using context pooling, customizing transactions isolvl to read uncommitted, using assplitquery, MARS, or AsyncEnumerable APIs.
I think this video deserves a part 2. Thanks anyway. Keep up your great work. Always love and appreciate your content.
P.s. please explain the asnotracking thing.
AsNoTracking is a way to tell the EF Core that if the entries that were retrieved from the DB would be changed, I don't want it to save the changes.
Presumably, let's say that you make a GET request and after that, for some reason the model that was retrieved from the DB will be changed by some other method that you didn't know.
The next time when you make another request that calls DbContext.SaveChanges() you will also save the changes to the model from the GET request that you've done.
Now, AsNoTracking it's faster because, when doing a query from the Database, EF Core has something called if I remember correctly "Tracking context". That context also does caching for already tracked entities, but also adds an overhead to your query. If you do AsNoTracking, then you won't have that overhead of adding the entities that you want to the Tracking context or however it's called
There is no point in comparing raw SQL queries in EF Core because it defeats the purpose of using EF Core in the first place. Context pooling would also make it worse because here we are reusing a single DB connection on serial requests. Read uncommitted is a DB-specific setting so it's irrelevant.
I have the same thoughts on comparison with raw ef calls. If those results are comparable I do not see any reason why someone would use dapper.
Also what about places where more complicated readings needs to be done. As with dealing with complex writting down to database, having easy way to write complex reading is also benefitial.
@@nickchapsas Well, there is db.Database.SqlQuery(someSQLQuery) which maps the result of a pure SQL query back to entities. That is pretty handy for some use cases and surely has it's place.
@@nickchapsas I don't think it defeats the purpose of using ef. It still does the model mapping which is the sole purpose of using dapper in the first place. So if ef provides that means out of the box, it should be given the fair shot.
You are correct about the pooling on the context of the current benchmarking scenario though.
how dare you release a non-gpt related video
I am sorry, will take it down
@@nickchapsas Is that an AI generated answer? :D
Actually I'm glad to catch a break a bit. I hate it but I know I have to use it to keep up. Still understanding software is needed
Bleah! I'm tired of the topic.
Haha.. true that😂
I think when EF Core stops using reflection entirely and move into source generators we would see a huge drop in the memory consumption
i won’t be satisfied until the build process triples the assembly size of my projects
@@MRender32
Oh, wait, you do want that? So copy and paste your code three times, then you will have a project with three sizes of what it is now. If you want to to it with build process you can do a lot of things, Grpc was doing this a lot before source generators was even a thing, even microsoft itself, and fody, just pick a tool and go for it.
But at the end, I think your commentary should not be exactly there.
EFCore won't be able to go reflection free without the work of 3rd party authors.
while EF core is generating some Expression-trees, which could be removed by generating the exact code neccessery beforehand in many cases,
the expensive part is still the translation from said expression tree to 3rd-party SQL language.
thus any Db-Adapter like MySql/Maria, Postgres, Mssql will need to be source-generating aswell, what we learned so far is, source generators are non-recursive, they can't modify existing code or see other source generated code.
In the end, we would have to pre-generate the expression trees, and use typed queries to be able to go full source generating.
which would make queries look more like `db.Users.QueryByFirstNameAsyn(firstName)" where QueryByFirstNameAsync is an extension method that uses a partial UserQueryByFirstName-Class which is source generated to include the exact neccessery Ado.Net code.
But this still only is the db access part and quite easy. EF Core does a fair bit more than that.
Do a video about AsNoTracking pls
I switched from EF to Linq2DB and never looked back. I would love to see a comparison in speed and ease of use between these 2.
Right choice 👍
I was literally thinking about going through this same exercise today! Thanks Nick, you're content is on point as always AND now I have a video for reference.
Very much interested in seeing more about ef core. Particularly the AsNoTracking() and AsSplitQuery() options espesially on objects with complex relations
Thanks for the video. Just one remark, before everybody runs away to refactor the data access layer and switch from EF to Dapper: Can we let it sink in for a moment, that these tests provide measurements using nanoseconds or microseconds? I'd suspect, that the actual overhead introduced by the ORM is mostly insignificant, as soon as your ORM of choice has to speak with something that's not living on localhost?
Haha good point.. our company gave us a database server so slow I suspect it's a 386 with 6mb of RAM. Developing on my local PC, a process may take a second whereas running it on the remote DB takes a minute or more.
We decided, when implementing CQRS, to use EFCore for commands and Dapper for queries, it seems we made the right choice.
I worked on a project like this and the dapper queries worked perfectly. However, as usual the EF side of things ended up being a mess and a detriment to the project - just the same as every project that uses EF I've had the misfortune to work on! Just use Dapper everywhere and put EF in the bin!
I think this is reasonable. EF works well for tracking changes for saves. But it is a nightmare to work with for queries that are anything more than get by id
I personally like the detachment, if my application requires rebuilding and deploying because I need to change a query, it'll drive me insane. So I stick with Dapper and Procs, just makes more sense to me at scale. Fine use EF to prototype but in production, and at scale, its more of a pain.
@@dwhxyz i think you use EF in the wrong way. In what case is better the raw query instead of the EF Select(x => new {.. method, with autocomplete, reference, ecc? Yes if you add a field you need to map that field and rebuild the project but probably that field have some logic and you need to rebuild your project in any case, for example adding that field on a form view. With EF your code is more OOP instead the classic row querys that are "just strings"
Yep, this is the way
I will be happy to see more comparisons for complex queries like joins and grouping or try new EF Core features like ExecuteUpdate and ExecuteDelete, I think in those areas the gap between them would extends. Maybe I'll perform those benchmarks myself.
Really nice unoppinionated comparison!👍
What I'd like to add is that the real downside of EF Core are Bulk/Batch updates, e.g. Upserts for 10k rows (you can make it efficient with it as well but the API for this is very clunky imo).
Fantastic comparison. Looking forward to seeing the video about AsNoTracking
wonderful analysis thank you!
I would love to see AsNoTracking memory usage explanation as well
Thanks!
My last job we used EF6 and I thought it was slow. My new job we use Dapper and I think I love it. It forced me to learn more SQL.
Man you read my mind. Yesterday I was thinking about this. And was planning to check by myself this weekend. Thanks!
I'll continue to use Dapper, thanks. The last time I used EF I was writing esoteric extensions to make sure EF produced correct queries and not something monstrous with unnecessary joins and other clauses. It was a waste of my time. Nowadays I just write clean SQL and map the results with Dapper.
And it's not like EF could have evolved from then.
To be fair EF has improved massively through core6 and core7. We were seeing some odd queries with nested selects etc, but it’s all pretty clean these days. Also, I’ve been surprised to see some of the query optimisation it does. It’s come a long way.
Amazing what having some (partial) competition in the form of dapper has done for encouraging EF to get better. I'm sticking with dapper and hand-crafted SQL though.
Interesting results… we use EF as a SelectOnly provider to dynamic request. As we dont want to “build” a query dynamically, we use EF to built if for us. The rule is : if it’s a static request : Dapper, EF otherwise.
I would be very interested to see how you operate with dapper and dynamic query that can change with querystring filter for instance.
Interesting comparison, but in real world examples, a proper ORM always beats having to mix business logic in both your service tier and database tier. Additionally using NHibernate (and to a lesser extent EF Core), you have the best of both worlds as you can use your mapped classes for general CRUD and when you want a more efficient query, you can use Criteria or HQL queries or with EF as well you can use SQL queries, which is then effectively the same as using Dapper.
Hi Nick, I really appreciate the time and effort you put into this stuff. I would love to see you introduce and compare some of the other tools available.
Would be interesting to see what you'd see with ExecuteSQL in EF Core which would be a simple way of having only one toolset that got you most of the way to dapper.
Awesome video Nick!
I’m curious about something with EF. How are you managing reporting with a lot of nested of join. That the hardest part to generate something effective and performant
Hi Nick, querying tables and including FK relationships in the results via Dapper always feels very bloated vs querying FK relationships with EF. Since you prefer Dapper for Read-queries, I would love to see a video on your hybrid setup with a more complex database structure
To be honest that's pretty impressive, EF core is really good as of now, but i don't think it will ever match Dapper in performance/efficiency. Simply because EF Core has way more stuff to offer that makes your life easier, i don't mind if it's 20us slower than Dapper
Before a year or two we have rewritten our API to CQRS where queries are using dapper and commands are using EF Core. Works flawlessly tbh.
Sincerely if you care about speed you will most likely not use an ORM. However its really good to see these benchmarks.
EF is wrapping a lot more. With dapper you have to do a lot of the general query generation, the template of it. If performance is your #1 goal, sure, it's better.
That loss of performance I've easily become OK with it because of the very short onboarding of team members due to less complexity of EF, and the lesser amount of mistakes coming out in pull requests.
I use EF Core where I work (though we still have a lot of code in sprocs). I like EF from a maintenance standpoint, but from a performance standpoint I have my concerns. I am hoping EF gets to a point where it can combine the entire unit of work and generate a single SQL statement when do you SaveChangesAsync. I don't find the issue with EF is how long it takes to execute a single query, but rather all the round-tripping and chatter that is required to perform operations of any complexity. That is where it really compounds.
I was waiting for you to compare the new EF methods such as ExecuteDelete and ExecuteUpdate with dapper methods, these two new methods are insanely fast compared to the old way of EF core.
You should've used EF Core's new `ExecuteUpdate` and `ExecuteDelete` methods for the delete and update benckmarks.
Thank you Nick, I think EF Core has greatly improved these days. Using the best of both worlds makes is more sensible. EF Core Add/Delete/Update and Dapper Filter/SELECT stuff
I suggest you make an overall ef vs dapper video, suggesting why and when to use one over another.
It would also be nice to see a comparison of Update and Delete using EF¨s new ExecuteUpdateAsync and ExecuteDeleteAsync methods. I think they are MUCH more memory efficient.
Have you also tried using linq2db instead of dapper? It's goal always was to be one of the fastest ORM Mapper with linq support. In some scenarios it also was faster than dapper.
we are the odd ones out at work and use nhibernate :D
but interesting to see them compared, I thought EF Core was a bit closer to dapper, but I guess it's only to be expected.
A video about AsNoTracking would be great!
This is a great test. I would like to point out that I typically see more complex queries for reads (selects with 3 or 4 inner joins) in both Dapper and in EF. Of course the creates, updates and deletes have to be per entity. I suggest that adding the complexity of joins to query a hierarchy of entities would yield a more practical measure of performance. Plus, it'll allow you to demonstrate and compare EF and Dapper in a more complex case.
Also, do you have any architectural suggestions for combining Dapper for reads and EF for the writes?
Thanks
The queries are kept simple to show the best case scenario. The more complex query generation gets, the slower EF gets
Thanks for your continued work with all the videos :)
I think that Dapper's problem is that it is dependent on the syntax of the database being used, whereas using EF (assuming you have the drivers), you can change the database under the hood without having to change all the SQL statements (since they're not used), but at the cost of some inefficiency. Please correct me if I have made any inaccuracies
How often is the backend database changed ? I've never seen this happen in my nearly 30 year career!
@@dwhxyz I agree with you on this, generally it remains the same, but it's not guaranteed that it won't change in the future. Additionally, you may have different types of databases (e.g. in unit tests) and want to use the same methods but with a different database that may lack some functionality. The convenience of not having SQL logic in the backend and leaving everything to the EF libraries to manage this part is certainly there, allowing one to focus only on functionality. I use both Dapper and EF, and to be honest, Dapper is convenient in some ways and not bad, but in terms of clean code, refactoring, and eventual (Although, as you said, it is quite remote as a choice or as a phenomenon that can occur.) replacement of the database, EF remains, in my opinion, an excellent framework.
@@dwhxyz Well, every couple of years? I've worked on 3 different projects that had to support exactly those backends that the customer provided and that had been certified by the authorities.
@@lupf5689 On the whole it is the exception. It's very rare and unusual for a large organisation running a large enterprise solution to change their backend database during the life of that solution. Regardless - given the choice I personally would not waste my time with EF.
@@dwhxyz I agree you!!! I am using EF at the moment... but just because it was already there. It is definitely not my favourite. When someone wants to use EF or dapper... come up with reasons "why" one over the other but NOT because of a DB vendor lock -in. Chances are that if your company switches from one DB to another say from Oracle to MSSQL or even from RDBMS to a NoSQL.... you are doing it for some significant reason. And it's "nice" that you could "just replace" the underlying DB but it then does not guarantee that you stick with the exact same performance. And that even implies you CAN actually translate your DB schema from one DB to another.
EF for writes is amazing. The data modeling and migrations is superb. For reads, it depends. Writing non-trivial efficient queries is hard enough. Adding the LINQ abstraction over that query generation and hoping the server's query optimizer will like what EF produced is too much of a headache. I appreciate that EF added raw SQL as a first class feature, but if I am going that route I prefer Dapper. The hybrid approach give you the best of both worlds with the fewest headaches.
This was great, but I wonder if you could also add using raw SQL queries from EF. I suspect that if you do the queries for Dapper and EF will be about the same performance. After all, the difference (simplistically speaking) between a Dapper query and a EF FindFirstOrDefault is the dynamic SQL creation. I suspect that it accounts for most of the time differences and memory differences.
It wouldn’t matter for this benchmark. We are comparing EF’s expression conversion to Dapper’s query execution, because that’s how people use them.
I wonder how using Compiled queries would influence the EF Core results. Maybe an idea for another video Nick?
I updated the pinned comment to include the results of compiled queries. I will make a dedicated video on them too!
@@nickchapsas looking forward to that video on compiled queries, sounds interesting
I'd love for you to compare linq2db, When I tested it years ago it seemed to me to have a lot of the benefits of EF but the speed of dapper, or much closer to that speed.
I would like to see a part 2 where you compare with Linq-to-sql and even raw sql (no Orm)
Very interesting video i prefer Dapper over EF Core. It would be nice to have compare joined queries which can be a litle slow.
Great! Really want to see a video about AsNoTracking
For measuring the performance of add and delete separately, you could use Postgres with a table that does not have a PK nor indexes. the performance won't be affected (at least for several millions of rows). But seeing that the performance of both is pretty similar, i'm not sure that this is worth it
I prefer dapper because I grew up with SQL...
For database compatibility, I usually use only basic queries in dapper and access views I create on the backend...
Also, I have found that if your queries are really complex, PostGreSQL optimizer tends to be less efficient. I have found that I sometimes need to create my own temporary tables far more often in PostGreSQL. And although EF can be handheld to access a specific function it is really complicated and dapper is actually easier to understand.
I appreciate you using, and I identify with using dark themes. Great video again. Thanks Nick for sharing with us.
That's why long ago, I started using best of both worlds.
Select query via Dapper, other data changes via EFCore.
As someone who hasn't done much with SQL in C# (believe it or not) and it's arguably not the point of this video, I'd be interested in hearing more about this hybrid approach of using both Dapper and EFCore at the same time for their strengths. Is it just a simple matter of using EFCore to generate the databases using its migration systems and then using the model objects created by the migrations for the output objects in Dapper?
No, it's pretty much a CQRS-like approach where you use EF for anything that involves modifying data and Dapper for read-only processes. A very simple example would be using EF for POSTs and PUTs and Dapper for GETs in an API. I say process because most of the time you will need some kind of read before or after a change in the database so for that scenario you would use EF to select the entity and track the changes. I've been doing this for years and IMO is the best approach by far, it makes DDD and CQRS very easy. Regarding the second part of your comment, you shouldn't be exposing EF's models to the outside since they are domain models. The best approach would be to map Dapper queries to DTOs specifically created to display data. This also makes it much easier to map the results since you don't really need the whole entity with nested classes to display simple things like, say, a user and its country. I.E: User.Name + User.Address.Country.Name (Domain Model) vs UserName + CountryName (DTO). You can now simply map the result of the select query to those 2 fields instead of building the whole entity.
Great video, is here anywhere I can download the code you built in this video to have a play around with myself?
The code is provided to my Patreon supporters
After several load test my setup is:
Ef core for the mutation
Dapper +sql kata for the quey
Could be interesting add this query builder on the benchmark for see if I have to remove it 😂
Hey Nick! Thanks for the comparison of these.
In a previous video (sorry can't remember offhand which one) you had mentioned that you avoid using Stored Procedures, and instead use an ORM. You said this was because using SPs encourages poor programming practices. Is it possible you could visit this topic sometime and explain what poor practices this encourages?
Thanks for all the valuable insight you provide!
Many moons ago stored procedures was the norm. The main reason was because you could secure the application account to be able to execute stored procedures only. This gave a nice layer of security should the application account be compromised - an attacker would not be able to dump or manipulate the database directly. It also meant that an application could not feed intentionally (like EF!) or unintentionally (due to a bug) the database with horrible SQL statements or harmful SQL statements. The DB admin could either write the stored procedures themselves or review any that were written. It also gave the added benefit that in a live environment a DB admin could update stored procedures on the fly without deploying application code should the immediate need arise. I'm not convinced moving away from stored procedures was a good thing but most people have been conditioned to believe it was based on stuff they are told or read/watch on the internet!
@@dwhxyz I used to write applications with Access front ends that were entirely run by stored procedures and triggers. There are certain things to be careful about when doing it, but it worked when following those rules. That just an extreme example from long ago, but these days I prefer to keep my SQL in my database code project as either views or stored procedures and have my C# code without any SQL in it. Just a personal preference and it works fine for me.
@@haegor I also did a fair amount of MS Access front end with Sybase/SQL Server back end work back in the 90's. I remember when I first installed and MS Access V1.0 in ~1992, connected it to Sybase followed by generating a few forms with the wizard. My mind was well and truly blown that I could all that without a single line of code!
@@dwhxyz It definitely was the norm. If you are around for a while, like myself, then you know this! The points you are making were indeed many times the drivers. And people who have never experienced stored procedures that way don't understand it. This is/was also especially powerful when 'consumers' is not only a programmer/code but you also were using things like MSAccess or Excel to query into databases. And another MAJOR point was that networks were REALLY slow and workstations were also underpowered and you were better off running an expensive/complicated SQL statement or set of statements together on the server and only return the smallest possible set of data.
And yeah I can see that people today don't "like" them from the perspective that these stored procedures and views are encapsulating business logic, and that reading just the code is not enough. Other than that... even still today! any database access and slow query performance can be addressed with the use of stored procs and/or views.
It'd be interesting a video about AsNoTracking
There is something interresting about the result of the first part. Could be cool to see how dapper performs if it used the same sql query as the entity framework find method generated.
There's something that is totally left away in this tests, which is initialization... while dapper has none, creating the DbContext will take its time, and that, specially when you're working with AWS Lambda or Azure functions is a very important performance hit in the cold start cases...
Comparing ef core to dapper is like comparing a simple knife to a Swiss army knife. Especially if we think about writes you should add some explanation why ef core is less memory efficient, what is it doing behind the scene (tracking changes of entities, generates query based on changed fields and so on). Without understanding trade off in functionality vs performance (speed and memory efficiency) this is purley academic discussion, being far from real life scenarios.
What about NHibernate? Would be interesting to see. At work we're using hibernate extensively, and I got really confused by the split of configuration between annotations and builder syntax in EFCore (some things are annotated on the class, some are provided via the builder syntax; Hibernate uses annotations for everything). I got it working eventually but it felt super weird. Regarding performance: in my experience, the table layout in the database decides 90% of the performance you're going to get. If your layout is well-aligned with your use case, the ORM has a decent chance to generate meaningful queries. If not... well functionally you may get the same result, but it will cost you in performance.
I have some experience with NHibernate and even tried to push a few changes to make it faster.
The main problem of NHibernate is Hibernate. When I checked the source code last time it was the result of conversion from Java. In addition NHibernate tries to be compatible with enormous number of databases. As a result the code is not well optimized.
One of the advantages was that NHibernate generated way better SQL code in comparison with EF6 (btw, EF core has pretty decent quality of generated SQL) and had way better extensibility (for example, cache).
I can also strongly disagree about table layout. Yes, this is important but EF core easily kills all your efforts by simply adding "ORDER BY" 😂 EF6 was even worse.
Any consideration kept for the LINQ to SQL conversion using EF-Core?
How big of an impact on overall web app speed this has in real world setup (the database is on remote server)? We are still talking about micro seconds here. Gaining extra 30 micro seconds of speed when added to something like 20-50ms on top of the time needed to execute a query over network and receive the response is almost not noticeable. 20ms + 10/1000ms vs 20ms + 40/1000ms. It ends up 20.01 vs 20.04 which is not that drastic at all. To me memory allocation is bigger factor here. If I can save 8-10 kb of memory per request that is huge, especially if we have high traffic.
here we go again :). This argument always comes up when Nick does one of these measurements. Your suggested real world setup, with your API running on server X and your database on server Y on a (local) network or even remote somewhere on the internet. While that is definitely "A" scenario, it is not the only one!! If you run your webAPI inside IIS on the same server with MSSQL then that network latency immediately goes away. Every developer knows that these types of setup are notorious for adding latency. Your network administrator might not like it but from the application and system design you don't want any of these types of latency. And then the other thing to think about is that others are doing this in cloud environments where such latency may or may not be as bad either. That is why Nick explains the system in the beginning, and that the read and write speed to the database is eliminated. If that becomes part of your system design where in the cloud you can be guaranteed to have something back from your database in 1ms or even less, then the additional time is way closer and therefore more significant. Also in the cloud, depending on your setup, when you work with virtual processors. if something gets achieved in 40 microseconds or 80 microseconds it translates to vCore usage and basically will be twice as expensive. You can run twice as much of this query now without having to scale, which effectively saves you money
I often in different projects facing with the issue when we have some foreach and for instance getting some data by id, make some changes for fist entity, and then next one is have same FK for another instance and entity is already tracking issue caught, how do this in right way do you have some video regarding this topic?
How do you get SQL recognition and coloring for your SQL queries that are strings? Is it just Ryder?
While this is interesting, most queries aren't going to be this simple. The joins start getting complex and you start including 4+ tables. I suspect EF will slow down quite a bit. Though, I like writing SQL, so I like using Dapper for my selects and I like using EF for my saves since that is normally a 1:1 table sort of query that I don't have to maintain anymore. Like you said, beset of both worlds. :)
In my opinion, EF enables quicker coding and simpler maintenance, although it may not be well-suited for extensive projects, it is perfectly suitable for most business applications.
Would you mind benchmarking bulk insert/update/delete?
Thanks always, Nick.
What version of EF Core was used in this test? Also help share the version of Dapper used. Thanks.
Latest in both versions
I was wondering, I see you have EF first and then Dapper wouldn't the first query be slower than the second executed query? when you reverse the order will it still be the same? If it's a shared connection wouldn't EF open this during their initial load?
The connection never shuts down and both tests have the same warmup period. It doesn’t run once but thousands of times to get the average execution time
I would love to see a comparison with Linq2db. It's proposition is quite interesting.
@nickchapsas maybe now video about RAW queries in the new EF 8? They return unmapped types with basic SQL queries. It's exactly the same as what dapper do. I'm curious if it'll be a dapper killer feature
They did some speed improvements in EF Core 7. I might have missed this detail, were the tests performed on EF Core 6 or 7?
Nick can you specify what versions of EF core and dapper you are using for future reference?
Usually like most videos Nick does but this one, i might question some of his results. Testing for perf and other related stuff, and would assert tends to be very hard to do right and might assert for DB stuff this may not be that good. Too many factors come into play, usage patterns, load, concurrency, startup, long haul to think of off the top of my head. To see one operation for doing and add and tearing everything down, only to rise and repeat, could easily see any DB will allocate a lot of memory. Why they are optimistically getting enough memory for its internal cache, real world usage at start up will possibly incur a one time perf hit to help speed up later operations... Honestly the last EF i used was 6.2 or so, i remember on it perf was really bad and spent time looking at various settings, in the end got one of our data imports from 20 minutes down to around 3 minutes. Just seems from what i saw with the benchmarks demonstrated, could easily see where EF would be slower and doing a simple cmd.Execute() that Dapper i think is doing is really light weight, so ya Dapper will probably always win in these. But long haul heavy stress loads with multi-users, which might be more typical in the real world, wonder if Dapper would still be as performant? I like Dapper for the things i've done with it but wonder if the usage patterns changed..., still as performant? Just saying for any benchmark to be valid needs to mirror typical/average use cases.
Finally i was aking for this and it looks great
Its as fast as dapper if you compare with ef6 and scale the graph so that ef core and dapper are close to each other. But if you zoom in, you will see the slowness.
You could have used an in-memory SQLite database with "Data Source=:memory:". SQL conversion should still be happening, but there is much less speed variance for I/O.
a comparison with the new "SQL queries for unmapped types" from EF Core 8 Preview 1 would have been nice
I’m waiting for the full release to be out before I draw any conclusions for .NET 8
I do not see the executeUpdate in EF, It would be interesting to compare it with the Update in Dapper
Lots of videos lately, nice
Entity Framework is good at translating the query into SQL, but that obviously takes some resources. I don't know if Dapper can also do such translations or if you must always provide the actual SQL as a string. What will the results look like if you pass the RAW SQL via Entity Framework as you do with Dapper?
This is exactly, in my experience, always the problem with EF. I write a EF linq expression, looks good and then at the generated SQL to then do the face palm and think to myself "like why!?". Only to find myself re-ordering linq methods, which sometimes generates way different SQL, and all kind of other mods to the expression to see if I can get it to generate something that makes sense. Just last week I fiddled with a query for probably a good 45 min, until I decided F that... here is the raw sql which made me go from a query that was timing out to a query that ran under 100ms.
@@paulkoopmans4620 Seems what I thought EF is good at is infact the exact opposite. I wonder are there any libraries that does a good job at writing c# queries which is better than EF. Combining such library with Dapper seems a potential better solution for those not wanting to write raw SQL...
Which EFCore version did you used??? Please let us know.
I wonder, what if we use directly sql query in ef core(FromSqlRaw etc.) and dapper?
Hi @Nick Chapsas Where can I find the video's code? I just want to test it locally and see the outcome.
The code is available to my patreons
thank you!
4:15 SingleOrDefault means single or the default value of what you are trying to get, usually that is null because its a class when getting entities out of a db, but if it is something else like a non-nullable list of ints, lets say IDs, it would return the default value 0 if the int you are getting isnt there.
But the main point of single or default is to throw an exception if there is more than one.
how it works that he gets SQL syntax for string withing IDE?
Does Nick's voice remind anyone of Jen on The IT Crowd when she pretends she can speak Italian? Or is it just me? :D
Thanks, Nick! Awesome content!
But those EF queries are so intuitive to write. I also question EF core startup time vs benchmark; conventionally I always found EF & EF core heavy at startup
What about complex objects with 2 or more joins? I would like to see benchmarks about this too.
What is shown in the video is the best case scenario given that the more complex the expression, the more expensive the operation.
@@nickchapsas Fair - but it would still be interesting to see if those differences grow exponentially with the complexity of the query (ORMs are all fun and games until you have to do anything more complex than "select * from pubs where id = 1")
@@b33j4y But where would you stop? I think the answer there is even more opinionated and based on a lot of factors in your application and domain. If the code you are talking about is in some sort of hot path the answer is maybe to use one over the other, or even, both are not sufficient and you need to remodel my bad DB design, in order to get decent speed. Whereas if this is a piece of code that runs at 12:00 am generating some daily report in app down time; well you may not worry so much about slower mapping.
You can always continue running a similar benchmark where you will compare EF against Dapper and let them both map two related objects, then three, until you are satisfied. But if, and this is an assumption, Dapper maps faster and more memory efficient for one, I am guessing it will also be faster for two. Based on Nick also isolating the sql operations and that came out pretty much the same; my gut feel tells me EF is mostly loosing time during the mapping and tracking operations, which is why he also put that NoTracking debate to rest.
Your SSD may be able to do 5 GB per second in long, sustained transfers of large files, but database transactions are typically very small. The usage pattern and performance characteristics are very different. For a better idea of database performance, look at its rated IOPS.
Can you please make complete grapghQL video! And show us the best practices. Please please please
Understanding why No tracking is slower would be good. Also, where do you stand on using Store Procedures with EF or Dapper?
Wow I'm kinda stuck on 40-50 for months. How much time do you dedicate to it?
Could the results for Dapper being lower because ou SQLite cache, at least for the filter part. I mean, the EF ran first and SQLite kept the query in its cache, and then when Dapper ran, the results were already there to deliver much faster??
No, sequence doesn’t affect the results. They all benefit from the same execution instance
what about linq2db?
it light weight, but without any raw sql, i think it's better approach
How about compiled queries for EF?
how about running stored procedure with entity framework vs querying with entity framework, which one is faster?
i mean write same query with sql server stored procedure and run with entity framework vs write same query with entity framework(.net 8) and share the results.
also i have another question. does entity framework can handle more than a billion records?(like 500 million from one table select and then join another table with 500 million records)
i am just wondering how fast is it