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.
@@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?
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
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.
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.
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"
Very much interested in seeing more about ef core. Particularly the AsNoTracking() and AsSplitQuery() options espesially on objects with complex relations
@@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.
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.
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.
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.
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
@@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.
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).
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.
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.
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.
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
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.
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
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 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.
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.
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.
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.
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.
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.
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.
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
Рік тому+5
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...
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
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.
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.
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
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.
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.
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.
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, 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.
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.
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.
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.
What is it like when you execute raw SQL queries in EF Core? Because if its negligibly different or even better then surly there is no point using Dapper at all.
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 😂
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
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.
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.
Hi👋 , I apologize in advance for my English. In my practice, I have encountered a sufficient problem. Ef is good at everything except reading data. If you have complex queries, writing from via linq + expression becomes very painful. The power of naked sql is very lacking, and dapper comes to the rescue here. As a result, 2 libraries can easily get along in the project. Everything about reading => dapper, everything else => ef (migrations, unit of work, db configuration, and so on)
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...
@@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.
Okay, i love Nick videos and i could learn a ton of them, but sometimes i feel that instead of actually being neutral and teaching us new tools or good ways of doing things he does try to "sell" us something, like Mediator on the Events video. Even't were doing exactly what he told them to do but he complained that it was a wrong behaviour and shoved mediator to "fix it". Here i feel a bit the same with Dspper i see it as an interesting tool and a performance target for ef core but the test made were not fair. On ef core you can execute raw sql too if you need and it wasn't tested. More opinions here, even then, is the code in the backend good enough to actually have the bottleneck im the orm? Usually on the apps i have worked on the bottleneck was either on the database itself or in the logic at the backend. Having 2 ORMs instead of one means managing 2 hells instead of one. Dapper maybe it is still relevant because it "secures" jobs for some developers that are focused on dbs, those that relies on stored procedures for everything because otherwise i see no real point om why would i want to use dapper myself especially when i can avoid jumping between languages. Feelings apart, good code on any of them will be most likely good enough for most of us
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.
@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
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.
The fact that databases itself are slow is the reason why EFCore is not optimized much. There is just no point as your db will fail first on high load scenario. Usually db over the network responds in 50ms, while EF takes time to compile query and so on for extra 0.04ms.
This. At least once a year one of our junior developers suggests, that we should use ORM X or switch to NoSQL Y after they spent a few days proving that it performs 10x better ... against local host. -.-
I'm actually curious to see this too. This video is a great look into how efficient both EF Core and Dapper are, but that's relative to each other. It would be interesting to see the total picture, e.g. when querying a database that's on another different server.
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?
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. :)
Amm it looks not representetive if we choosed to use EF we benefits from complex object parsing, could you compare complex objects with joins, also would like compare bulk and batch updates
@@nickchapsas Yup but it will be not really differenece i mean you should care abount network latency for that cases more then executing time, most critical is complex quering
Well with Dapper you are fiddling yourself with SQL statements. So why do you use Dapper instead of a native (to the used database) library? IMO you use EF in order to get some abstraction in order to not have to fiddle with SQK statements so Dapper and EF isn't the same type of library.
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
EF did do better than I expected in your benchmarks. The problem is that in real world applications, developers use EF as an excuse not to learn SQL or anything about databases. So it's those who don't understand how a database works that are the ones using EF, and they end up writing bad code that generates really bad queries. e.g. pulling too much from the DB (such as the entire table) then to make it worse they use linq to filter it in C# code for the rows they actually needed. Speaking from my experience on people I work with who use EF that think they are so-called seniors.
I think there are more reason than "not knowing better" for the use of EF or any other ORM. We currently have to support 4 different DBs, since that is what our customers have deployed. Sure as hell, I wouldn't want to manually maintain migrations and a bunch of slightly different queries for each and every statement. That said, we actually do handwrite a limited number of SQL queries, since these are critical for the performance of the whole system and I have yet to see an ORM that can produce something with similar performance. And for quite many EF translated queries, we at least made it a habit to check the actual SQL output to ensure they are at least ok.
I disagree. EF makes for way cleaner code so in a majority of applications the performance hit is worth it, it's not about laziness. Dapper gets messy very quickly if you're doing anything more than simple operations
I agree that everyone who use an ORM should understand database basics, however, SQL is not entirely unlike C/C++ in this setting, great SQL is great, but most developers don't write great SQL, in fact, most SQL production queries I've seen are a horrible and inefficient mess. C# with LINQ greatly increases the base level of quality in SQL queries and database interactions.
@@lupf5689 I can definitely see the appeal if you are supporting 4 different databases. So far, I've worked on applications that use different databases but never more than one type in a single application.
@@CabbageYe That's ok - I guess we'll have to disagree. In my experience, I've found applications that use it hard to optimize. When the application's performance is poor, I'll monitor what queries are running on the database and identify slow queries (to use my example again, where clause is not specific enough and/or whole table loaded into memory) and then suggest improvements to the developers who are working on that application. However, it then takes them a long time to figure out where in the application that query is being executed as there may be several places that table is accessed. If EF is meant to be easier and cleaner maybe they just don't know what they're doing?
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
Is there really value in micro-benchmarks and differences measured in tens of microseconds? Is the time spent hand-rolling all SQL queries really worth the tiny improvement that you won’t even notice if you add all the other logic and overhead?
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.
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.
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 👍
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
Very much interested in seeing more about ef core. Particularly the AsNoTracking() and AsSplitQuery() options espesially on objects with complex relations
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.
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.
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.
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.
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).
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.
Do a video about AsNoTracking pls
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.
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.
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.
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.
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
Fantastic comparison. Looking forward to seeing the video about AsNoTracking
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.
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
Sincerely if you care about speed you will most likely not use an ORM. However its really good to see these benchmarks.
I suggest you make an overall ef vs dapper video, suggesting why and when to use one over another.
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.
You should've used EF Core's new `ExecuteUpdate` and `ExecuteDelete` methods for the delete and update benckmarks.
wonderful analysis thank you!
I would love to see AsNoTracking memory usage explanation as well
Thanks!
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.
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
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.
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.
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.
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.
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.
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.
I would like to see a part 2 where you compare with Linq-to-sql and even raw sql (no Orm)
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.
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
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...
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
Man you read my mind. Yesterday I was thinking about this. And was planning to check by myself this weekend. Thanks!
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.
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.
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
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.
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.
That's why long ago, I started using best of both worlds.
Select query via Dapper, other data changes via EFCore.
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.
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, 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.
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.
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.
A video about AsNoTracking would be great!
It'd be interesting a video about AsNoTracking
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.
What is it like when you execute raw SQL queries in EF Core? Because if its negligibly different or even better then surly there is no point using Dapper at all.
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 😂
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
Very interesting video i prefer Dapper over EF Core. It would be nice to have compare joined queries which can be a litle slow.
Thanks for your continued work with all the videos :)
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.
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.
Hi👋 , I apologize in advance for my English. In my practice, I have encountered a sufficient problem. Ef is good at everything except reading data. If you have complex queries, writing from via linq + expression becomes very painful. The power of naked sql is very lacking, and dapper comes to the rescue here. As a result, 2 libraries can easily get along in the project. Everything about reading => dapper, everything else => ef (migrations, unit of work, db configuration, and so on)
Great! Really want to see a video about AsNoTracking
I would love to see a comparison with Linq2db. It's proposition is quite interesting.
How do you get SQL recognition and coloring for your SQL queries that are strings? Is it just Ryder?
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...
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.
thank you!
I do not see the executeUpdate in EF, It would be interesting to compare it with the Update in Dapper
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
Okay, i love Nick videos and i could learn a ton of them, but sometimes i feel that instead of actually being neutral and teaching us new tools or good ways of doing things he does try to "sell" us something, like Mediator on the Events video.
Even't were doing exactly what he told them to do but he complained that it was a wrong behaviour and shoved mediator to "fix it".
Here i feel a bit the same with Dspper i see it as an interesting tool and a performance target for ef core but the test made were not fair.
On ef core you can execute raw sql too if you need and it wasn't tested.
More opinions here, even then, is the code in the backend good enough to actually have the bottleneck im the orm? Usually on the apps i have worked on the bottleneck was either on the database itself or in the logic at the backend.
Having 2 ORMs instead of one means managing 2 hells instead of one.
Dapper maybe it is still relevant because it "secures" jobs for some developers that are focused on dbs, those that relies on stored procedures for everything because otherwise i see no real point om why would i want to use dapper myself especially when i can avoid jumping between languages.
Feelings apart, good code on any of them will be most likely good enough for most of us
I appreciate you using, and I identify with using dark themes. Great video again. Thanks Nick for sharing with us.
Would you mind benchmarking bulk insert/update/delete?
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.
@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
Finally i was aking for this and it looks great
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.
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?
Which EFCore version did you used??? Please let us know.
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
The fact that databases itself are slow is the reason why EFCore is not optimized much. There is just no point as your db will fail first on high load scenario. Usually db over the network responds in 50ms, while EF takes time to compile query and so on for extra 0.04ms.
This. At least once a year one of our junior developers suggests, that we should use ORM X or switch to NoSQL Y after they spent a few days proving that it performs 10x better ... against local host. -.-
I'm actually curious to see this too. This video is a great look into how efficient both EF Core and Dapper are, but that's relative to each other. It would be interesting to see the total picture, e.g. when querying a database that's on another different server.
You compare apples to oranges. Compare Dapper to EF Sql Interpolated. I also miss Upsert benchmark. And Bulk operations.
Not at all. I am comparing two ORMs in their critical paths of how people use them. This was always the argument.
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?
I wonder, what if we use directly sql query in ef core(FromSqlRaw etc.) and dapper?
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!
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. :)
Any consideration kept for the LINQ to SQL conversion using EF-Core?
Amm it looks not representetive if we choosed to use EF we benefits from complex object parsing, could you compare complex objects with joins, also would like compare bulk and batch updates
What is shown in the video is the best case scenario given that the more complex the expression, the more expensive the operation.
@@nickchapsas Yup but it will be not really differenece i mean you should care abount network latency for that cases more then executing time, most critical is complex quering
@@nickchapsas would like to hear about difference between EF and EF.FromSql vs dapper for plain queries
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
Well with Dapper you are fiddling yourself with SQL statements. So why do you use Dapper instead of a native (to the used database) library? IMO you use EF in order to get some abstraction in order to not have to fiddle with SQK statements so Dapper and EF isn't the same type of library.
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
Nick can you specify what versions of EF core and dapper you are using for future reference?
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
EF did do better than I expected in your benchmarks. The problem is that in real world applications, developers use EF as an excuse not to learn SQL or anything about databases. So it's those who don't understand how a database works that are the ones using EF, and they end up writing bad code that generates really bad queries. e.g. pulling too much from the DB (such as the entire table) then to make it worse they use linq to filter it in C# code for the rows they actually needed. Speaking from my experience on people I work with who use EF that think they are so-called seniors.
I think there are more reason than "not knowing better" for the use of EF or any other ORM. We currently have to support 4 different DBs, since that is what our customers have deployed. Sure as hell, I wouldn't want to manually maintain migrations and a bunch of slightly different queries for each and every statement. That said, we actually do handwrite a limited number of SQL queries, since these are critical for the performance of the whole system and I have yet to see an ORM that can produce something with similar performance. And for quite many EF translated queries, we at least made it a habit to check the actual SQL output to ensure they are at least ok.
I disagree. EF makes for way cleaner code so in a majority of applications the performance hit is worth it, it's not about laziness.
Dapper gets messy very quickly if you're doing anything more than simple operations
I agree that everyone who use an ORM should understand database basics, however, SQL is not entirely unlike C/C++ in this setting, great SQL is great, but most developers don't write great SQL, in fact, most SQL production queries I've seen are a horrible and inefficient mess. C# with LINQ greatly increases the base level of quality in SQL queries and database interactions.
@@lupf5689 I can definitely see the appeal if you are supporting 4 different databases. So far, I've worked on applications that use different databases but never more than one type in a single application.
@@CabbageYe That's ok - I guess we'll have to disagree. In my experience, I've found applications that use it hard to optimize. When the application's performance is poor, I'll monitor what queries are running on the database and identify slow queries (to use my example again, where clause is not specific enough and/or whole table loaded into memory) and then suggest improvements to the developers who are working on that application. However, it then takes them a long time to figure out where in the application that query is being executed as there may be several places that table is accessed. If EF is meant to be easier and cleaner maybe they just don't know what they're doing?
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
Understanding why No tracking is slower would be good. Also, where do you stand on using Store Procedures with EF or Dapper?
what about linq2db?
it light weight, but without any raw sql, i think it's better approach
Is there really value in micro-benchmarks and differences measured in tens of microseconds? Is the time spent hand-rolling all SQL queries really worth the tiny improvement that you won’t even notice if you add all the other logic and overhead?
Depends on your performance requirements. For me it was, yes.