3 ms·

>We have far more problems with too many .Includes causing terribly performing queries with bad JOINs than lazy loading problems. Granted this code base is in a

>We have far more problems with too many .Includes causing terribly performing queries with bad JOINs than lazy loading problems. Granted this code base is in a bit of a state and we have a complex order structure that can go like 10 layers deep, and ideally we're looking to go even deeper with complex pricing. You do an include with all of that and you're going to get a terrible query. Wait - are you "Include"ing things you don't need? If not...assuming a sane query plan, shouldn't the single query (e.g. "Include" version) outperform the deconstructed series-of-queries that brings back the same data? E.g., Included: context.Orders.Where(x=>x.OrderId = 5).Include(x=>x.OrderItems) which translates roughly to select * from Orders o join OrderItems i on o.OrderId = i.OrderId where o.OrderId = 5 vs. var order = context.Orders.Where(x=>x.OrderId = 5); var items = order.OrderItems; which translates roughly to select * from Orders o where o.OrderId = 5; select * from OrderItems where OrderId = 5; As far as I understand the former will outperform the latter, even if you don't take into account the additional connection overhead. If the second version was faster...wouldn't SQL just compile down to a series-of-queries automatically?

mattmanser 9y ago
The example is too trivial, which is why it looks like it might be better. Here's a real world example, not even complete. A restaurant booking might have: - A venue associated with it - A user who booked it - A menu -- With courses (starter, main, dessert) --- of Menu items (steak) ---- with Menu item option groups (think, 'pick one of', 'choose at most 3', etc.) ----- of Menu item options ('rare', 'medium', or 'extra chips', 'onion rings') - Guests -- Guest.User -- Guest selections of menu items --- Guest selection options - And Many more! (payments, events, offers, postal addresses, etc.) There are loads of things that are optional, or even extremely rarely filled in (say, an associated special area of the restaurant, or perhaps an assigned waiter, or a third-party partner who placed the booking). And we can just let the EF load that in lazily. It knows a nullable int means nothing to load, but if there is a third party supplier, it can go off and load that lazily (and extremely cheaply). As for the query, you can load them in chunks (which we do), but the way the EF works you're limited on how you can do that. If you try loading that all in one go, you get a very, very slow query. It's beyond the limits of the execution planner to do it well. Because of the nature of ORMs, the EF can also make decisions which result in horrible sub-selects, or terrible joins of sub-tables where the execution planner can't use the right indexes, especially when you're trying to do groups, counts, sums, etc. This makes it often better to load things separately and to selectively use the lazy loader to do certain things. Other scenarios include where say you have a complex object that you've only partially filled in, but in 1 in 5 cases you want to send an email using that object. Now you could write your email function to load all the data again, or you could let lazy loading do its thing and, overall, save time and decrease db load, because you've already got 80% of the data, it just needs to fill in the missing 20% with some simple queries. Answer to earlier question: I use to hand-code my db upgrades as my opinion is that having correctly structured data is king and I understood relational db design. But it turns out EF Migrations are wonderful when you know how to use them. I still check every single one to make sure they're doing exactly what I wanted and expected though, and take them up and down manually. Good way of catching mistakes.
- Mouse47 9y ago
  I see - so in your case you gain from the fact that many of the joins are likely to result in zero matches. And since you're joining on a nullable FK, you can tell in advance whether the record exists without a lookup - I ran a test and I verified that you pay a cost for the below join regardless of whether the FK column is null. select * from Person p1 --p1.Spouse is null for the record in question join Person p2 on p1.Spouse = p2.PersonId where p1.PersonId = 42 (I understand why it can't do it in the execution plan, but I'm surprised it doesn't 'short-circuit' at runtime since the join predicate is trivially unsatisfiable for that row) Your other benefit involves conditionally needing data. I will say it's not too hard to structure app code to avoid loading redundant/unneeded data in your email example, but it's certainly easier and more maintainable when property access is fundamentally linked to its actual retrieval - it's impossible for another developer to make changes to your version and 'lose' the efficiency, while the same isn't true for mine. So it's less black and white than I thought...which it usually is :) How do you feel about using 'explicit' lazy loading? E.g. PersonEntity person; if(IWantToSendEmail){ person.Reference(x=>x.Email).Load(); //use email info here } This might be the best of both worlds...
  - mattmanser 9y ago
    It actually is quite hard to do it and have re-usable code. There are various different ways the same email might get triggered, maybe the booking came from an API call, maybe it came from a new booking form, maybe it came from a 'send reminder' button. In all cases, I have a booking object that will be in a different state of being filled in. The underlying need for data for the rest of the request is very different. Some of them need a fully filled in booking, some of them need the bare essentials. Our "fully load this booking" function takes like 150ms, which isn't cheap and a significant amount of the time of that is DB time, which is again our most in-demand resource. CPU/Memory is (generally) under-utilized on web apps and letting the EF do it's lazy loading thing is usually the best solution. As for explicit lazy loading, it's inelegant and way more code. One thing we know for sure, more lines = more bugs. I'm not saying turning off LL is a bad thing, if it works for you, but I semi-regularly have a SQL profiler running while developing so I see when it starts kicking out loads of queries un-necessarily.

mattmanser 9y ago

The example is too trivial, which is why it looks like it might be better. Here's a real world example, not even complete. A restaurant booking might have: - A venue associated with it - A user who booked it - A menu -- With courses (starter, main, dessert) --- of Menu items (steak) ---- with Menu item option groups (think, 'pick one of', 'choose at most 3', etc.) ----- of Menu item options ('rare', 'medium', or 'extra chips', 'onion rings') - Guests -- Guest.User -- Guest selections of menu items --- Guest selection options - And Many more! (payments, events, offers, postal addresses, etc.) There are loads of things that are optional, or even extremely rarely filled in (say, an associated special area of the restaurant, or perhaps an assigned waiter, or a third-party partner who placed the booking). And we can just let the EF load that in lazily. It knows a nullable int means nothing to load, but if there is a third party supplier, it can go off and load that lazily (and extremely cheaply). As for the query, you can load them in chunks (which we do), but the way the EF works you're limited on how you can do that. If you try loading that all in one go, you get a very, very slow query. It's beyond the limits of the execution planner to do it well. Because of the nature of ORMs, the EF can also make decisions which result in horrible sub-selects, or terrible joins of sub-tables where the execution planner can't use the right indexes, especially when you're trying to do groups, counts, sums, etc. This makes it often better to load things separately and to selectively use the lazy loader to do certain things. Other scenarios include where say you have a complex object that you've only partially filled in, but in 1 in 5 cases you want to send an email using that object. Now you could write your email function to load all the data again, or you could let lazy loading do its thing and, overall, save time and decrease db load, because you've already got 80% of the data, it just needs to fill in the missing 20% with some simple queries. Answer to earlier question: I use to hand-code my db upgrades as my opinion is that having correctly structured data is king and I understood relational db design. But it turns out EF Migrations are wonderful when you know how to use them. I still check every single one to make sure they're doing exactly what I wanted and expected though, and take them up and down manually. Good way of catching mistakes.

Mouse47 9y ago
I see - so in your case you gain from the fact that many of the joins are likely to result in zero matches. And since you're joining on a nullable FK, you can tell in advance whether the record exists without a lookup - I ran a test and I verified that you pay a cost for the below join regardless of whether the FK column is null. select * from Person p1 --p1.Spouse is null for the record in question join Person p2 on p1.Spouse = p2.PersonId where p1.PersonId = 42 (I understand why it can't do it in the execution plan, but I'm surprised it doesn't 'short-circuit' at runtime since the join predicate is trivially unsatisfiable for that row) Your other benefit involves conditionally needing data. I will say it's not too hard to structure app code to avoid loading redundant/unneeded data in your email example, but it's certainly easier and more maintainable when property access is fundamentally linked to its actual retrieval - it's impossible for another developer to make changes to your version and 'lose' the efficiency, while the same isn't true for mine. So it's less black and white than I thought...which it usually is :) How do you feel about using 'explicit' lazy loading? E.g. PersonEntity person; if(IWantToSendEmail){ person.Reference(x=>x.Email).Load(); //use email info here } This might be the best of both worlds...
- mattmanser 9y ago
  It actually is quite hard to do it and have re-usable code. There are various different ways the same email might get triggered, maybe the booking came from an API call, maybe it came from a new booking form, maybe it came from a 'send reminder' button. In all cases, I have a booking object that will be in a different state of being filled in. The underlying need for data for the rest of the request is very different. Some of them need a fully filled in booking, some of them need the bare essentials. Our "fully load this booking" function takes like 150ms, which isn't cheap and a significant amount of the time of that is DB time, which is again our most in-demand resource. CPU/Memory is (generally) under-utilized on web apps and letting the EF do it's lazy loading thing is usually the best solution. As for explicit lazy loading, it's inelegant and way more code. One thing we know for sure, more lines = more bugs. I'm not saying turning off LL is a bad thing, if it works for you, but I semi-regularly have a SQL profiler running while developing so I see when it starts kicking out loads of queries un-necessarily.