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 😁

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

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

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

Thank you and awesome explanation.

Thanks for this video explanation

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

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

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

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.

Nice 👏

Ace Milan Jovanović

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.

great feature!

Nice tutorial, you got a new subscriber 👍 How did you enable logging SQL queries in console? I need this

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! 🚀

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

What did you do to get the sql in the console/output of the IDE?

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

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

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.

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

place matters?. if i place as split at the bottom (before materializing) does it change anything?

Hi! I'm again here to make the "scalability" question. Using EF as it manages his own connection pool different than ADO. What should we do to configure max connection pool and queueing to prevent EF from crashing? A collegue found an issue where I try to access to an endpoint simulating 1000 concurrent users simultaneously with a simple query done with ADO and EF. ADO gets to 100% pool but it always returns an answer but EF gets to 100% and FAILS to return, it looks like it manages wrong the queue, any help here? By the way, if you select all columns in EF it will also go way slower than selecting the ones you use and use way more memory (for 1 to many relations with big results this does a very big difference).

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?

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

Im having troubles scaling net ef core, i cant do more than 200 rps. With Nestjs with same data structure, db and etc .. im doing 5000 qps, can you show us how to tune it for heavy loads ? Im using postgresql

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

How about if we use Implicit Join instead of include? does AsSplitQuery has any benefit in that case? I assume when we use Implicit Join "AsNoTracking" isn't required as well, am I right?

First time u sent it, postman recorded 30ms, then split query & it became 638ms, how is this an improvement? Just curious.

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. www.milanjovanovic.tech/

time goes from 30ms to 652ms as soon as you add the split query

not so sure about this improvement. sending multiple queries to the engine involves the latency needed to process each one. there is not only the latency introduced by network (that is some situations means not a local server but a TCP connection over the network), but also the time needed to go into the steps of parse-optimize-reduce-generate plan query cycle. For relatively small queries this time is comparable to the time to effectively execute the query itself. Plus with multiple queries you tend to prevent some optimizations the SQL Query engine could perform when the query was specified only once.

Please measure the speed for us?

Or we can, actually write sql and not worry about performance :) ?

Very informative video, but what a horrible hack to bypass SQL faults

Over the past year I've started moving away from EF. I love the code first concept and how easy EF is to use. However, that ease of use is (from what I'm seeing) making a lot of devs lazy and they end up not understanding what the tool is doing when it builds the queries.

When you do this you are no longer guaranteed that you'll get a consistent result. If something has changed that data between your two calls you'll get weird results.

To be a program efficient, it should not use any framework like ef. Only something li dapper is on a brick of efficiency.

😂 query splitting…