One Trick To Improve EF Core Performance Using Query Splitting

In this video, I'll show you how to improve EF Core Performance using Query Splitting. Query splitting is an EF Core feature introduced back in .NET 5. I'll show you how to use Query splitting to improve performance, and how to configure EF Core to use Query splitting at the DbContext level. Also, I will talk about the pros and cons of using Query splitting with EF Core.

One Trick To Improve Entity Framework Performance

Join my weekly .NET newsletter:

Subscribe for more:

Chapters
0:00 What is Query Splitting?
0:25 Setting the scene
2:08 Single SQL query
3:54 Query Splitting in action
7:03 Behavior with missing records
7:58 Configuring Query Splitting on DbContext

[MilanJovanovicTech]

A few people pointed out that the second version is "slower", which you can see around the 4:30 mark.
That was a request hitting a cold start, unfortunately.

I tested it now and:
- Single query: ~8ms
- Split query: ~5ms

I admit that I may have made an error here, leading you to believe it is in fact slower with query splitting.

As I said in the video, measure everything and never take anyone's (including mine) word as the "truth".

I'll try to do a better job with this in future videos!

Stay awesome 😁

[onursedef]

From 21 seconds to 5 seconds. I am pretty impressed by this feature. Thank you for showing.

[escobar]

Never noticed this capability before, so appreciate it being called out. I often just split queries by hand and join in memory. As you say - often a complex query is slower than multiple smaller ones. I will certainly look at this splitting capability though, it might save me some effort.
Not strictly NET related, but since you mentioned the potential cost to multiple queries depending on proximity of data, I would mention the most impressive complex query performance remedy I have been using quite a lot recently, is using FOR JSON. In a corporate environment with MS SQL cluster spanning datacenters, consumed by APIs also spanning datacenters - we aren't always ensured of a short trip from client to the current primary node. By structuring the data as JSON from the SQL we reduce the replicated column data and the resulting payload is a fraction of the original rowset. Since JSON is a bit chatty itself, we actually use a SQL scalar function - to generate the raw JSON result (text) and then compress the result (byte array). Its simple in code to decompress what SQL has compressed with stock NET Gzip (IIRC) implementation, and deserialize to the resulting object type. You end up with a structured result like what you would do in EF projection, but tightly compressed to flow back across the wire to the client - at the cost of CPU rather than IO. I have yet to dig in to how SQL does it, but in all tests to date a FOR JSON query with many subqueries performs best (for us, YMMV) when compared to a single query with numerous joins to related data expressed as a rowset.

[erosnemesis]

This feature greatly improved my performance 10x! Maybe not that much but it was immediately noticed

[MrJonjoe]

First time I've come across this. Looks like it could be useful for some larger ef queries. Much appreciated

[sergiom.]

Wow I am discovering your channel and I have found extremely useful things

[fernandocalmet]

Very interesting, this lesson improves a lot in query performance. Thank you very much for sharing Milan👏😁

[zeeshanasghar]

very informative, will implement in my project. thanks for sharing another wonderful video

[jaomartins]

Entity could just use the gathering id to fetch attendees and invitations from database, instead of doing a join with inner query 😅, nice video by the way, keep up the good work! 🚀

[mahmudx]

I usually do load nested objects when it is required one by one. After just getting an object by Id, I do some checking, if it passes, then I load the nested object when it is required. It saves some time, I think. I never benchmarked though.

[atifsaeedkhan]

Hi, Just one question, I can see multiple includes are being used, what if I want a parameter of list<strings> for all these 3 includes and include them dynamically? How to achieve it?

[RajK]

Nice tutorial, you got a new subscriber 👍

How did you enable logging SQL queries in console? I need this

[jawadahmed]

please make a detail video on layered architecture using identity and ef core

[ScottMaday]

Thanks for this Milan!
Is this effectively the same as doing 1 query for Gathering and then doing:
await => g.Attendees).LoadAsync()
await => g.Invitations).LoadAsync()

[KarwanEssmat]

Well done,
I created separate queries for both ( single and AsSplitQuery). I just invoke inneeded one.

[ibrahimal-zaidi]

Hi Milan,
I have a question, currently I'm working on a big API project to build API for a database with 114 tables.
So:
1. who can I generates Dtos for all Entities?
2. Do I need to build controllers for all tables? all Entities ? or just the Important one?
3. Any design guide or ideas I will appreciate that.
Thx.

[ramytawfik]

another question please:
Can you please tell me the difference between both queries ?

var result1 = dbContext.Authors.Include(x => x.Books);
var result2 = from author in dbContext.Authors
from book in dbContext.Books.Where(x => x.AuthorId == author.Id)
select new { author, book };

[MilanJovanovicTech]

Every Saturday morning, I send one .NET tip in my newsletter.
*2625 .NET engineers* already read it. I would love to have you with us.

[tasin]

How do you test the split query vs single query performance properly locally? What if the actual app service and db servers are close but the local environment is on another server with more latency?

[muhammadsaifullah]

How can we generate sql query in command line window.?