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. 😃

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

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 👍

Excellent video. I did not know about include statements with filters and the significant performance improvement

Great video, more challenges like this would really help

Thanks you for all. I take much pleasure to learn new thing from you.

You nailed it Milan, I love how your explanation with benchmark. Im sure ppl out there will get benefit of that

Awesome video. The way you explained is very clear and feel like everyone was waiting eagerly for climax.

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.

wow you have got such a great ability to explain things very clearly !!! subscribed 👍

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.

I've been following you on LinkedIn for a while and happy to see you making such videos. This was fun. Keep rocking!

Love the video, hearing you think it through reinforces my understanding of building queries

Awesome tutorial with a lot of useful efcore information. Thanks a lot!

Great video and explanations! Well done!

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)

Thanks Milan, stay strong !!

I am a .NET developer , I like your concept. It is helpful really great job

Thanks for this wonderfully explained video. Very useful

Thank you for this amazing playlist. Hello from Armenia

Hi Milan, thank you for this. You really explained very well.

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.

This was fun 🔥

Great Job!

Thanks Milan, Very nicely explained.

Can you explain please 6 stet: Filtered Include? I don't understand why it faster?

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

Great video, thank you Milan

Hello from russia. Thx for usefull videos. Think i'm ready to start your course this fall

This is a very good post and video. Subscribed right now. Thanks. More videos please.

Your content is great and it relates to me.

wow it's amazing

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 ;)

Awesome video!

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.

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

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.

Wow really nice video. Would have been cool to see the raw SQL query as well.

There is always the case with frameworks to optimize them. Because of it hard to maintain them in big projects.

Thanks...

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?

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.

great video!!

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.

You could still stay within the rules, use EF and use plain sql queries. With that, got over 306%

Managed 253.92x improvement in speed and 1533x memory usage reduction :)

Amazing! thank you! 😁

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

Hey Milan, very well explained. One small query. How did you get that performance matrix console window results?

Hi Milan great video, can you do in future comparation of dapper and entity framework speed of certain query executions

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.

Great video. What's that tool you use to measure and display the linq performance?

Good Work🥰

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

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

Thanks for video. Great job. Could you recommend some books to read for deep learning Entity Framework

As a video game dev, we strictly prohibit the usage of LINQ because they are notoriously slow and generates TONS of garbage to collect.

Thank you! I hate ORM .. prefer mappers 😉

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.

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?

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 😢

Why removing includes do not yield any significant improvements?

In select getting allbook can we use extension method to get books list? somewhere i tried but navigation property not working in extension method.

How did you configure BenchmarkDotNet to show the Faster calculation column?

If you add "covered indexes" you will get a bit better result ))

Should be empty list, no? Because how can an author aged 27 have books published before year 1900?

How about compiling the query?

Nice video... Curious what will be time for Dapper with 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 ??

man i want to know who the maniac coding like this, seeing ToList() being put everywhere almost make me got a stroke

in your experience, do you find yourself using EF more than raw SQL?

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

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

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

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()`

(surprised pikachu) You can filter inside Include statement??

try this same query in .NET 7. It's superemely fast

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

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

The include filter was good tho

LGTM!

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'm Beginner 😢

Strange video thumbnail, are you 3?

It is time Microsoft drops EF.

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()

why not just make a stored procedure and call it? this code seems so ugly..