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 👍
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
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.
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.
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)
@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
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.
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
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?
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 ;)
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.
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.
@@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.
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.
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
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.
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.
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
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
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.
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.
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 ??
@@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.
@@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.
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 😢
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()`
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
@@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 ;)
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 :)
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
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
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
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.
@@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.
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
@@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
@@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 ;-)
@@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.
@@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.
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()
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.
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! 😁
wow you have got such a great ability to explain things very clearly !!! subscribed 👍
Thanks Rakesh, I'm glad you enjoyed this video.
Awesome video. The way you explained is very clear and feel like everyone was waiting eagerly for climax.
Glad you liked it!
Thanks you for all. I take much pleasure to learn new thing from you.
Glad to hear that!
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 😁
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 for this wonderfully explained video. Very useful
You are very welcome :)
Love the video, hearing you think it through reinforces my understanding of building queries
Part 2 coming tomorrow 😁😁
Great video, more challenges like this would really help
Thanks! I'll see if I can give Stefan some ideas.
Great video and explanations! Well done!
Thank you very much, Kristopher!
Hi Milan, thank you for this. You really explained very well.
Awesome, I'm glad you liked it
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.
Awesome tutorial with a lot of useful efcore information. Thanks a lot!
Glad you liked it, I'm making some more EF Core videos
Great video, thank you Milan
Thank you Vedad, I'm glad you liked it
Thanks Milan, Very nicely explained.
Do you have any idea how we can make it even faster?
Thanks Milan, stay strong !!
Stay awesome buddy!
Great Job!
Thank you! Cheers!
Awesome video!
Thank you!
Your content is great and it relates to me.
Thanks a lot, Amit!
great video!!
Thank you!
This was fun 🔥
You should try it also, maybe you have a better idea 🦄
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
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 👌
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 😁
Thank you for this amazing playlist. Hello from Armenia
Thank you too!
@@MilanJovanovicTech I will wait new amazing videos )
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
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 😅
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.
wow it's amazing
Thanks :)
I am a .NET developer , I like your concept. It is helpful really great job
Glad it was helpful!
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
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 ?
Great video. What's that tool you use to measure and display the linq performance?
BenchmarkDotNet
Hey Milan, very well explained. One small query. How did you get that performance matrix console window results?
I'm using BenchmarkDotNet
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!
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.
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.
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"
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.
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 😁
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
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 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/
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
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.
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.
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
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.
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.
in your experience, do you find yourself using EF more than raw SQL?
I use EF more often than raw SQL
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 :)
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.
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.
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
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 :)
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
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 😁
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!
(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.
Managed 253.92x improvement in speed and 1533x memory usage reduction :)
Wow, fantastic Daniel! Great job 😁
What did you do?
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 😁
Why removing includes do not yield any significant improvements?
Because I'm using a project (Select)
Thank you! I hate ORM .. prefer mappers 😉
I'm not a fan of mappers 😅
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 ;)
If you add "covered indexes" you will get a bit better result ))
Against the rules of the coding 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 :)
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
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.
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 🤣
try this same query in .NET 7. It's superemely fast
You should try, and submit a PR to the 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
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
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!
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?
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?
LGTM!
🦭 of approval
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 🤦♂️
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 ;-)