Making Entity Framework Core As Fast As Dapper

hello everybody I'm Nick and in a previous video we compared the performance of anti-firmware core and Dapper to see how they compare nowadays because it's been a while since we were in the old ef6 days that things were really rough and I want to see if the claims of the EF core team and Microsoft were true and spoiler alert they largely wear mutations like add update and delete were almost as fast or as fast in some scenarios as the same operation using Dapper and that was very surprising now one of the things that was lacking was memory EF core was more memory hungry but the speed results were very surprising however querying reason filtering was not as good it was actually pretty bad in this video I'm going to show you how you can use one of EF core's more interesting features to completely change this to the point where EF core is as fast as Dapper if you layer of content a you want to see more make sure you subscribe during the sub notification Bell and for more training check out nickjobses.com alright so let's quickly recap and I will have a link to that previous video in the description Down Below in the top record of the screen right now if you want to watch that first it's a great video really explaining the methodology as well of why I did things the way I did in the benchmarks I won't be going too in depth here so check that first if you want the full picture but where we were basically was that we used sqlite and we had this movie very simple object to use in EF core and Dapper and the reason why it's kept simple is to give as much of a best case scenario to EF core as possible of course more complicated objects with joints and stuff will mean that EF core will get slower but I just want to see how good the basic scenario is if you want to see a more complex scenario in the future leave a comment down below and then we'll use sqlite because I didn't want to basically Benchmark how fast the engine can run in my machine I want to test something that runs on the disk I have a very fast disk that this will not be the bottleneck and really what we're testing here is the conversions that happen within EF course so the query generation effectively and execution so this will give it best shot it can now in the benchmarks I'm using a exceeded random class to make sure that the same data is generated deterministically I'm using a movie generator over here and I'm sharing the connection as well to again give EF call the best case scenario it can have and then what I'm testing for here is the single or default call of EF core the first or default call of EF core and then the query operation of dapper now you might be wondering if you watched that previous video Nick where is find EF core find you could actually do dot find and find something by its key and you'd be right if you were to be retrieving something by each primary key you really should be using find however I also want to show you what it would be like to check for any other property now the reason why I have both single or default and faster default is because there's actually a difference between what is generated behind the scenes single or default because of how single works in the context of C sharp will have a limit of two or a top of 2 2 in the query however first or default will add a limit or top of one so even though conceptually simply makes more sense if you're getting something that has a unique value you should be using first or default not single or default now of course if a column is not indexed if it is indexed then it doesn't really matter and then in Dapper because I do control the query it doesn't really matter I just have singular default here because I know only one value will come back now I have already run these benchmarks as they are now and as they were in that previous video and where we stand is basically this so single and first effectively the same performance so first it's just a little bit faster because of that limit but for the PK it doesn't really make much of a difference and then we have a 6.8 kilobytes of memory as opposed to 2.2 in Dapper and quite a bit of a difference in performance so we have 34 microseconds or 35 microcycles here and 7 microseconds in Dapper which for many people that can be big depending on what performance means to you now can we get this 3435 closer to the 7 of double the answer is yes surprisingly close and we're going to do that with something called compiled queries now what is a compiled query well when you say something like this to EF core and you say take this and give me the data back if core will do a bunch of operations involving expressions and conventions of those expressions to a SQL query that eventually will be executed against the database and that operation takes time and memory so what we're going to try and do here is actually just compile the queries once or compile this operation once on application startup and then keep them in memory and reuse them so what does that look like well I'm going to start with the single and I'm going to duplicate this method and I'm going to say compile here and I'm going to put the compiled field which we're going to be tracking just above it so what I'm going to have is a private static in this case read-only as well failed and that is going to be a function a delegate so the way this will work is it is a func that accepts first the type of the context so in this case movie context because that is what we are using to get to the database and get the DB set and use it and then if you have any parameters in this case we have this ID we're passing down then the type of this ID and then the return type now you have two options with ef core queries you can have synchronous or asynchronous queries since this operation is async I'm going to use an async operation as well so the result will be a task returning a nullable movie in case the movie does not exist and that's my Fields type now now I'm gonna call this single movie async and I'm going to create the compile query so I'm going to say EF the static class and then compile async query you can also have a compile query in here I'm going to have a compile async query because again this is an async operation so let's split that because it can get complicated so we have first the movie's context over here so context and then we have the parameter of the good is the ID and I'm gonna have my Lambda and then use the context over here and say dot movies Dot and then I'm gonna have my operation but I'm not going to use single or default async the same thing I'm using here because for async queries you actually want to use the synchronous version and it will be turned into async in the background so I'm going to copy the same delegate here and I'm going to say and equals that ID and that's it now I have that compiled query and now I can just put a semicolon here use that and this whole thing now is return a weight single movie async and we pass down the two parameters so the movies context and the test movie dot ID and that is it so now I have this cached field which is a function that has this compiled query that I can now use in this operation now I'm also going to do the same with first the code is based the same the only reason I keep first here is so you can get a view of both how they scale and how they differ so now I have both single and first using those compile queries and I'm gonna go ahead and just run these benchmarks to see where we stand now with performance so results are back and let's see what we have here so as you can see now and this is so cool we went from 35 microseconds all the way down to 10 for both operations and the memory strike from 6.8 to 2.75 that's a huge difference which is so close to Dapper almost the same and I would argue that yes it is very impressive that the two now differ just by three microseconds but also this memory difference will be something that you would also have to pay in garbage collection pauses and now you don't have to so for Sim scenarios like this EF core just makes so much sense to use it's very impressive that they managed to optimize it that much now of course in more complicated scenarios with joints and groupings and stuff like that this can get bigger here and we're gonna see that in a future video actually if you want me to make that video leave a comment down below but I would say that for most people the speed of development you're getting by using EF core doesn't have to cost you now in runtime costs in terms of memory and speed just super super impressive stuff and we're not gonna stop there I'm also going to add some filtering here because okay it's fine when you return a single movie but what happens if you return seven movies in this case well let's see all right so I removed all the other benchmarks here and I only have these two which both of them are just filtering so here I'm using a where clause and a two list async to match any movie with a year of release equal to 1993 and this I know will return seven movies for both benchmarks and I have the same thing with Dapper so we have these two benchmarks over here and I'm going to quickly run them to see how Dapper an EF core compare in something that is just general query not a single item so results are back and let's see what we have here so EF core filtering is 32 microseconds almost 33 and 7.8 8 kilobytes of memory Dapper filtering 12.39 microseconds and 3.84 kilobytes of memory so twice the memory for the same operation and almost three times the speed so not fantastic however you can also compile a query on an operation that returns multiple things so how can we do that well it's a bit different it's not the same as returning a list of movies but I'm going to just duplicate this method over here and I'm going to say EF filter compiled over here and then above it I'm going to Cache that field so it's still a function that accepts a movie context or a movie's context here but instead of returning a list of movies I'm going to return an i async enumerable of movies and actually I could also pass down the year of release as a parameter to make it more of an equal comparison so in here and I'm gonna just say get movies async I could say get movies by year acing whatever I wanted and then the method of compiling is the same EF dot compile async query we're still going to have the context and we're also going to have that int which is the year and then we're going to say context dot movies where and we're gonna use dot year of release equals to Year and that is that you don't say to list async or to list you're not gonna do anything like that because what we're going to do in that compiled method is actually create the construct we want to use to return it so it doesn't restrict us to a list you can choose to enumerate this in any way you want what we will do is just create a list to contain the items and then use an async for each loop on that get movies async function we just created and then add the results in that list and then just return it and that is it so now I'm going to run my benchmarks again and see how the three now compare so it's also back and it's what we have here so as you can see now we are way better off with that compiled query from 30 to 14 microseconds just 2 microseconds difference from that double query and very close in memory so compile queries clearly get you to almost the same performance as Dapper again in those simplistic scenarios so should you be using compiler queries everywhere well that's something for you to answer because those fields need to be allocated and they do take some time to execute now how long do they take let's add The Benchmark for that as well so I'm going to compare now how long it takes and how much memory it takes to compile that first movie async query with a simple ID check and then I'm going to have just a little bit more of a complex query it still doesn't have any joints but it has a select it has a wear and it has a first or default so you'll be able to see how a more complex query makes this slower and less more efficient hopefully so let's run this and see how to compare finally alright so results are back and let's already have here so the first movie async compilation 1.2 microseconds and 1.65 kilobytes of memory that it's going to live in your application basically forever and then this complex compilation just with that simple complex query we went up to 2.4 microseconds and three kilobytes of memory so depending on how many queries you have and how complex they are they can have an impact on your startup time so you have to keep that in mind and of course they also inflate the memory that you're going to have to maintain throughout the application because those fields will be treated as static filter or Singleton fields that you're going to have to carry around and reuse ultimately my advice is it's up to you you have the data and now you also have the means to know how to get the data for your own systems again I'm going to have a link in the description if you want to become a patreon to get the source code I'm going to add all the benchmarks back to run those tests for your own purposes but that's where this simple scenario stands and that's how we can get EF core to be almost as fast as Dapper but now I want to know from you did you know about compile the reason were you using them leave a comment down below and let me know well that's all I had for you for as well thank you very much for watching especially next to my patreons making videos possible if you want to support me it's only going to find the link description down below leave a like if you like this video subscribe as well and I'll see you in the next video keep coding

Channel: Nick Chapsas

Views: 62,069

Keywords: Elfocrash, elfo, coding, .netcore, dot net, core, C#, how to code, tutorial, development, software engineering, microsoft, microsoft mvp, .net core, nick chapsas, chapsas, dotnet, .net, .net 7, ef core vs dapper, ef core, dapper, entity framework core, entity framework, entity framework vs dapper, entity framework core vs dapper, ef core vs dapper performance, entity framework vs dapper performance, compiled queries, compiled queries ef core, optimising ef core, dapper vs ef core

Id: OxqAUIYemMs

Length: 13min 17sec (797 seconds)

Published: Thu Apr 06 2023