EF Core Performance Optimization Challenge | 233x FASTER
HTML-код
- Опубликовано: 4 авг 2024
- ☄️ Master the Modular Monolith Architecture: bit.ly/3SXlzSt
📌 Accelerate your Clean Architecture skills: bit.ly/3PupkOJ
🚀 Support me on Patreon to access the source code: / milanjovanovic
In this video, I'm going to be solving an EF Core performance optimization challenge. The goal is to optimize a poorly performing EF Core query and make it as fast as possible. My friend Stefan created the challenge, and there's still time to post your submission. How fast can you make this EF Core query?
You can find Stefan's EF Core performance challenge here:
github.com/StefanTheCode/Opti...
Stefan's LinkedIn (follow him for great C# posts)
/ djokic-stefan
Join my weekly .NET newsletter:
www.milanjovanovic.tech
Subscribe for more:
ruclips.net/user/MilanJovano...
Chapters
0:00 What is the challenge?
1:55 Improvement 1: Upgrade .NET version
2:10 Improvement 2: Don't load the entire Authors table
3:31 Improvement 3: Limit the number of Authors returned
4:46 Improvement 3: Filter the Books
6:11 Improvement 4: Fetch only required columns
7:48 AsNoTracking discussion
9:14 Improvement 5: Sorting before projection
10:08 Improvement 6: Filtered Include
12:02 Improvement 7: Query Splitting
13:12 Improvement 8: Get lucky 
Наука
Want to master Clean Architecture? Go here: bit.ly/3PupkOJ
Want to unlock Modular Monoliths? Go here: bit.ly/3SXlzSt
Hey MIlan. Thank you for solving my EF Core performance optimization challenge. You did a great job and explained it even better in the video. 😃
Thanks for creating the challenge. I'm already looking forward to the next one 😁
where can we see the challenges?
@@justind6983 Check the video description
This is an amazing video. I loved the way you went about deciphering each layer of the query and combining the changes with the benchmark results. Thanks for sharing such valuable insights 👍
You're welcome, Nilesh! I want to bring as much value as possible with my videos
Just a small note to everyone, may be it is obvious for most but always use the Async overload as much as possible (at least for all microsoft frameworks and libraries, for 3rd parts, it depends) => async/await end-to-end
It may not improve the performance (depend on a lot of things) but it is much better to manage the connections and anything cpu related
That's excellent advice, Anthony!
You nailed it Milan, I love how your explanation with benchmark. Im sure ppl out there will get benefit of that
Thanks a lot! 😁
Awesome video. The way you explained is very clear and feel like everyone was waiting eagerly for climax.
Glad you liked it!
wow you have got such a great ability to explain things very clearly !!! subscribed 👍
Thanks Rakesh, I'm glad you enjoyed this video.
Love the video, hearing you think it through reinforces my understanding of building queries
Part 2 coming tomorrow 😁😁
I've been following you on LinkedIn for a while and happy to see you making such videos. This was fun. Keep rocking!
Thanks a lot! I'm glad you are finding my content useful 😁
Really good explanation!!, it's pretty common to find this kind of issues when working on legacy code, and just by making this small changes you get a boost of performance that will be noticed by the users. Sometimes is more complex, other not so much, but is always a good place to start.
Thank you. The original example is intentionally bad, but as you said it can be common in legacy code or with beginners. Luckily, fixing it is easier.
Thanks for this wonderfully explained video. Very useful
You are very welcome :)
Great video, more challenges like this would really help
Thanks! I'll see if I can give Stefan some ideas.
Awesome tutorial with a lot of useful efcore information. Thanks a lot!
Glad you liked it, I'm making some more EF Core videos
Hi Milan, thank you for this. You really explained very well.
Awesome, I'm glad you liked it
Excellent video. I did not know about include statements with filters and the significant performance improvement
Note that they only work when loading actual entities. In my example, it won't work properly because I have a projection.
Thanks you for all. I take much pleasure to learn new thing from you.
Glad to hear that!
Great video and explanations! Well done!
Thank you very much, Kristopher!
Great video, thank you Milan
Thank you Vedad, I'm glad you liked it
Thanks Milan, stay strong !!
Stay awesome buddy!
Thanks Milan, Very nicely explained.
Do you have any idea how we can make it even faster?
Love your videos. One of the patterns I've used very commonly when dealing with returning complex datasets is actually pulling the root objects from the database first, in this case Authors. And then doing a follow-up query to pull additional metadata based on the filtered Authors.
For instance, I have a search api that returns products and child products (there's only 2 tiers) but there are 50,000 products to search. It's a paginated list so I pre-filter the parent products and execute the statement. Then I execute a child query specifically for the returned results.
It's not always a performance boost, but I find on very large datasets with a lot of metadata properties that need to come from the database, it can be faster to pull and then combine in memory.
That's a smart optimization technique 👌
This is a very good post and video. Subscribed right now. Thanks. More videos please.
Thanks a lot! I have plenty more on the channel you can watch, and lot coming in the future also 😁
@@MilanJovanovicTech
Your content is great and it relates to me.
Thanks a lot, Amit!
Great Job!
Thank you! Cheers!
Awesome video!
Thank you!
Dolazim iz istog svijeta kao i ti Milane :) Kad mi je ovaj video iskocio i kad sam pogledao na prvu "neoptimiziran" query, iskreno sam se zapitao koja je j***na budala napisala ovako los LINQ upit :) Ali valjda je namjerno to napravljeno kako bi se pokazalo da je cesto lako ubrzati query i to na nacin da se reorganizira malo query.
Tako je, namerno je lose napravljeno u startu 😅
Great challenge, didn't know about this
so i used your code as a base to improve the perfomance
i used "compiled queries"
this even improve the memory allocation in a 50% and the perfomance from 191x to 203x faster (didnt reach to 233x in my pc)
I wasn't aware of compiled queries when making the video. So that was a missed opportunity on my end. Gonna make a separate video about it 😁
This was fun 🔥
You should try it also, maybe you have a better idea 🦄
great video!!
Thank you!
Really nice video. The next step is to implement specification pattern to make the query easy to maintain and unit test.
ps: you can add AsNoTracking() too
AsNoTracking won't have an effect when we return a projection, don't forget.
Thank you for this amazing playlist. Hello from Armenia
Thank you too!
@@MilanJovanovicTech I will wait new amazing videos )
I am a .NET developer , I like your concept. It is helpful really great job
Glad it was helpful!
Can you explain please 6 stet: Filtered Include? I don't understand why it faster?
Actually, that's a slight mistake if you read the comments.
The filtered include doesn't work with projections, which I wasn't aware of.
So the reason is faster is that we end up filtering in-memory...
Thanks for your videos ! Very nice topic. I created the same query improvment before watching this video, thanks for being able to checking the solution
and one more thing - every string in Db has max length ( nvarchar(max)) - for me changing this, it's kind of optimazition too, first of all it will be affect to memory allocation but of course change execution time of first query (not-optimised).
All the best and see you in next video ;)
Did you try Compiled Queries?
@@MilanJovanovicTech , I didn't
@@MilanJovanovicTech I read about it and used it, execution time is almost the same, but memory allocation is less by 30%, thanks for tip
@@Dragonet17 I actually saw a nice improvement in execution time. Interesting.
@@MilanJovanovicTech using compiled queries or something else ?
Good Work🥰
Thanks a lot, Mohamed!
Amazing! thank you! 😁
No problem 😊
@MilanJovanovicTech could I ask you to bench the same tests on .net 4.5 , in the same host, so we can compare the performance between 4.5, or 4.8 vs .net core 6, 7 ,8 . The latest, something like it? 😁. That will be a nice video comparison for all community. Tô show if only the changing of versions bring some performance
Great video. What's that tool you use to measure and display the linq performance?
BenchmarkDotNet
In select getting allbook can we use extension method to get books list? somewhere i tried but navigation property not working in extension method.
Yes, you can use extension methods
wow it's amazing
Thanks :)
Hey Milan, very well explained. One small query. How did you get that performance matrix console window results?
I'm using BenchmarkDotNet
Hi Milan, a question. How may I make a dynamic Select statement using entity framework?. I'll put you in context. I want to select only the columns that I pass to my method
You have to write code that will generate the select statement. Not a trivial thing.
You can make your own using Expression API
OData can do that!! It's pretty easy to implement.
Would be interesting to see how fast the Dapper alternative is, 4ms is still very fast. So its a matter if its worth giving up on strongly typing to use raw sql to gain something like 1 or 2 ms.
I'll benchmark it to see, but I suspect the same as you. To improvement will be nothing compared to the roundtrip cost to an actual database.
How did you configure BenchmarkDotNet to show the Faster calculation column?
There's a Ratio setting or something, can't recall exactly. Check this: davecallan.com/how-to-set-the-ratio-column-style-in-benchmarkdotnet-results/
Wow really nice video. Would have been cool to see the raw SQL query as well.
It would definitely be the fastest version
@@MilanJovanovicTech I was expressing my self poorley. I meant the raw SQL query generated by LINQ on every benchmark test as an reference to what changed in the query. Although you where good at explaining.
Hi, Milan. It's a really good video with cool explanation of EF queries. I so appreciate it. And I really like your vs studio theme, especially code colors. Could you tell me how to set this up, please?
1. Install ReSharper (it's a paid tool 😔)
2. Use the ReSharper Dark theme
i couldn't find ratio on benchmarking when i do i couldn't figure out how can i compare like yours how many times its faster can you tell me the process of this ??
Take a look at this:
benchmarkdotnet.org/articles/configs/configs.html
@@MilanJovanovicTech thank you i got it :)
Thanks for video. Great job. Could you recommend some books to read for deep learning Entity Framework
Take a look at "Entity Framework Core in Action"
Hello from russia. Thx for usefull videos. Think i'm ready to start your course this fall
Sending my regards from 🇷🇸 :)
Be sure to check with your bank if Teachable.com is supported for payments. I've had a few people from Russia complaining that their payments failed. And of course I'm available if you have any questions about the course, you can always send me an email.
in your experience, do you find yourself using EF more than raw SQL?
I use EF more often than raw SQL
Nice video... Curious what will be time for Dapper with raw sql
Probably a bit faster, but nothing drastic. I can even check!
@@MilanJovanovicTech What about using Dapper with stored procedure??
How is it that when you apply the filtered include to the query, the query performs better? I always thought that when using a custom projection (as is the case here), EF ignores the calls to `Include()`
You are totally correct there! It was missed by me when recording the video. I did however fix in the PR on GitHub
Faced a similar issue when we tested on prod db. We had an SLA of 2 seconds but the query was taking more than 1 minute. We had to migrate to Stored Procedure and use multiple temp tables to reach the SLA.
2 seconds can be quite hard for some heavy queries
Maybe it could improve the performance a bit by using:
b.Published < new DateTime(1900,1,1)
to avoid fullscan on Book table, because the index was applied to date and not just year
There is no index, unfortunately 😁
Hi, can you please show an example of calling ef read and save operations from parallel.foreach which does not crash due to multiple threads?
Just create a separate database context inside of each iteration of the loop, that way you won't run into that issue.
@@MilanJovanovicTech I am creating dbcontext object using scope factory but it still fails. Do you have any sample code to share? Thank you.
@@microtech2448 Well, if it's the same scope it's going to be the same DbContext.
Just do something like:
Parallel.For(() =>
{
using var ctx = new MyDbContext();
...
});
@@MilanJovanovicTech thanks for sharing it but I wanna use dependency injection. From parallel.foreach I would want to call business layer which would have ef core operations. Also, I wanna avoid the use of new keyword as much as possible.
Hi Milan great video, can you do in future comparation of dapper and entity framework speed of certain query executions
Definitely, adding that to my list!
Thanks...
Anytime!
When I looked at the query results, there were books published after 1900. I don't think the filtered include is working. It may be faster because there is no filter at all in the SQL.
Indeed that is the case Christopher. I pointed it out in a few comments.
It's not working because we have a projection, that's why.
In the solution I submitted in the end, I just filtered the books in-memory. It was only slightly slower than in the video.
Filtering in include doesn't work everywhere, I had several cases where it still return all joined objects and I had to add filtering in where clause. Also there is an option to use AsNoTrackingWithIdentityResolution bacause it'll create less linked objects in memory. It might be slower than AsNoTracking but you''ll waste more time for garbage collection to clear all that mess. And you might face equality problems if don't use AsNoTrackingWithIdentityResolution
That's a fair point, Andrew. In my example, Filtered Include did not work at all. I found that out after recording the video unfortunately. Because it won't work with projections.
Hello Milan,
Thank you for the challenge but my EF Core skills are still at beginner level
and I am learning a lot from you!
I would like to know if you can point me to some good resources about Change Tracker
i kind of don't grasp its utility in regard to DB Context.
Thank you for sharing your knowledge, it is highly appreciated.
I don't think you need to dive deep into change tracker, other than high level understanding. I would look at Microsoft docs.
@@MilanJovanovicTech Thanks brother
What if by chance you wanted to load all records from a table? I’m only asking because I have event logs stored in a database and it takes 500 years to load them all 😢
Load them in batches with a few threads?
@@MilanJovanovicTech thank you Milan, I’m looking into how to do that. Thanks :)
There is always the case with frameworks to optimize them. Because of it hard to maintain them in big projects.
Not really when you know what you're doing
@@MilanJovanovicTech Sadly, it is not what you are doing the problem, it is what you have to do within framework to do what you need. Framework mostly is about constraints, especially ef core, not to mention it took ef team years to made it to run arbitrary sql.
Should be empty list, no? Because how can an author aged 27 have books published before year 1900?
Leaves this world at 27 years of age, and was born in the 1800? 😅
Magic!
Fitering the books on year becore counting them , can give a differend ordering, due to all the books before 1900 not being counted anymore.
Performance yes, differend results as well.
Note that these are different columns!
@@MilanJovanovicTech Right its not a count, but just a number.
I missed that. only listened to my brain.
I think you should try this query with a window query with rank function. It is possible that your data set may to small for this to be effective. but given a lot of data. window querys usualy perform better if you need to rank or order etc. this can be done with using linq2db efcore extension
That's an interesting approach, I'll look into it
Why removing includes do not yield any significant improvements?
Because I'm using a project (Select)
How about compiling the query?
Didn't try that as I wasn't aware of the feature at the time, but it adds quite a bit of a performance boost on top
So, the main idea is to squeeze all the work out of EF core and put it into the SQL server, isn't it?:) It's a junior level optimization.
As long as the video helps someone, I don't mind what you call it 😁
You could still stay within the rules, use EF and use plain sql queries. With that, got over 306%
Not possible before EF Core 8
@@MilanJovanovicTechOf course it is.
You could use something like:
using (var command = dbContext.Database.GetDbConnection().CreateCommand())
...
Then perform an Execute on your sql query.
Its technically, still entity framework ;)
(surprised pikachu) You can filter inside Include statement??
Yes, but it only works when you actually load the entities from the database.
I kind of f-ed up in that regard.
It could possibly be optimized slightly more by not fetching the country and age, since those are hardcoded at Serbia and 27 in this case.
But most likely that would have a very minor speed boost. :P
The filtering at database level will likely cost more, I doubt it will make much difference. But a fun idea 😁
Managed 253.92x improvement in speed and 1533x memory usage reduction :)
Wow, fantastic Daniel! Great job 😁
What did you do?
As a video game dev, we strictly prohibit the usage of LINQ because they are notoriously slow and generates TONS of garbage to collect.
What do you do instead?
@@MilanJovanovicTech prealloc and re-use list, hashset, some dictionary, and mostly achieve all that using for-loops.
.NET 7 should fix that.
Thank you! I hate ORM .. prefer mappers 😉
I'm not a fan of mappers 😅
man i want to know who the maniac coding like this, seeing ToList() being put everywhere almost make me got a stroke
It's on purpose to make the original query extra slow, relax 🤣
If you add "covered indexes" you will get a bit better result ))
Against the rules of the coding challenge
There is great explanation of common fallacies when building queries, and I used most of these when I improved a db query for just 5000 records from 3 minutes down to 0.5 seconds
But I still fail to feel that you know 100% what you're doing in all of those areas, for instance you failed to explain why we got a performance regression from an optimization change you made that you kept later on, and you also account for little adjustments in the ratio without accounting for the average error induced in benchmarking
For instance, seeing an improvement from 179.812x to 181.006x is only 0.664% better, which could very well be within the error of the results you get (at 9:52 it shows that the error of your result is 0.265% with the stddev being 0.247%), including potentially hidden error in the measurements like background tasks or whatnot
While this is not microbenchmarking as we're talking in the scale of milliseconds and not micro or below, I still fail to see some "progressions" as minor as this one as absolute wins, especially in the context of database querying which involves connectivity and could perform more inconsistently than you can imagine
Noted! I'm curious what you did to go from 3minutes to 0.5 seconds?
I think I removed an Include, used Contains inside a Where when there was a hand-written loop that was performing a join with multiple queries, added indexing based on date and pagination of the queried results
But thats not the point removing the includes a developer could require those to make joins of data. I dont really think removing them is the right answer
Includes don't work with projections
try this same query in .NET 7. It's superemely fast
You should try, and submit a PR to the challenge 😁
Hi Milan, I think you can change .Take(2) row before select DTO model. Because if you need just specific 2 rows and then you fetch 2 rows from DB after that you can use select method. Probably performance much better than before. By the way thanks for video :)
How do you think my version works?
It loads just 2 rows from the DB.
@@MilanJovanovicTech I checked again you are right, my mistake sorry :)
The include filter was good tho
The worst part is - it didn't work 😅
@@MilanJovanovicTech It didn’t? As in the data returned was wrong? That’s worrying
@@willinton06 It's how it works, actually. Includes don't make a difference with projections. I wasn't aware of that recording.
And there was one performance improvement left which I also didn't know about: Compiled Queries.
Gotta make a separate video about that.
Yay!
Thanks a lot for that cool introduction. Some additional improvements that may have a place:
1. To use Enum instead of "Serbia" like Country.Serbia. That will give faster code changes in the future.
2. Use smaller queries and return IQuerytable object. That flow will give us more flexibility in the code and in this way code will be more readable and understandable.
3. Use instead of ToList -> ToListAsync and return the async task. That will provide you more performance for 100 and more concurrent calls.
I like your's improvements and your explanation that was great.
Very well addition.
1. Good point
2. Make sense in some situations
3. It wouldn't make much difference for the benchmark
LGTM!
🦭 of approval
Sir, i want to know, why this video not same from source code? , like line 104 , in source code,GetAuthors_Optimized() was disabled and different line, i want to know how many you edited from source code, thank's
I may have made the code a little prettier before starting recording
@@MilanJovanovicTech Sir, do you have the tutorial manual config like your video , i saw ratio value is not 1.00 but baseline and 277 faster, how can make like this?
I guess you ran into a EF bug, perhaps even a major one. if you need to reboot and re-run your tests to get back 50x times then perhaps you should open a bug somewhere as i am sure you are having a massive issue that should not go in production, if anything having cashed data in the down-stream like sql cashing etc. should improve your data the more often you load it. if I loose ~1/6th of my performance after executing my code I would not think this was production ready. I would open a defect with Microsoft with your findings
Actually no, the regression was because we moved the filtering of books to the database, and there are no indexes in place.
@@MilanJovanovicTech things do not tend to get slower over time when there are no indexes, if you added indexes and that's why you got faster you should mention it that the difference was due to the database changes and not the EF code.
If that's the challenge why not put the database on a partitioning scheme and then the query would have taken a micro second. Nothing to do with the CODING challenge but hell, if we are cheating then lets cheat to win ;-)
@@vesnx So now I'm cheating? 😂
@@MilanJovanovicTech it's a coding challenge, you are not showing you are altering the play ground. What do you think, update the video and show you altered the database or update the video and only show the C# changes, the way you do it now is not reproduceable by any one that follows your shows.
@@vesnx But I didn't alter the database 🤦♂️
who did too many times write tolist in query? this is not optimization dude i don't think that no one write queries like that
Did you even listen to the intro?
I'm Beginner 😢
Only takes time
Strange video thumbnail, are you 3?
Of course
You clicked the video buddy
It is time Microsoft drops EF.
Never 😁
@@MilanJovanovicTech Learn ADO (1 hour ?) and you can do much more than with EF and it is always faster than EF because EF depends on ADO. So why does EF exist?
EF is ten years now? And still it has problems.
Dump EF :-)
I did the first 3 editions of EF and after that i was done with it.
Take it from me: stop using it.
What's your story pink glove?
ok here is my optimisation idea; db.tables.where(filters).include(xxx).theninclude(xxx).include(xxx).theninclude(xxxx).tolist(); this will be far more faster than db.tables..include(xxx).theninclude(xxx).include(xxx).theninclude(xxxx).where(xxX).tolist()
Sound plausible
why not just make a stored procedure and call it? this code seems so ugly..
Did you not hear this was a coding challenge at the start?
If you don't use Linq and instead use SQL everyday, the code is going to look ugly (or vice-versa). It's just a matter of specialized familiarity and bias.
@@MilanJovanovicTech ah, youre right ;-)