4 ms·

Django is really bad (good?) at abstracting this kind of stuff away. <ul> {% for friend in user.friends.all %} <li>{{ friend.name }}</li> {

Django is really bad (good?) at abstracting this kind of stuff away. <ul> {% for friend in user.friends.all %} <li>{{ friend.name }}</li> {% endfor %} </ul> The problem is that if you have 200 friends then it will make 200 queries. If you have 1,000,000 friends then, well, this will crash. The Django solution would be to fix-up your "user" queryset like: user = User.objects.filter(pk=<pk>).prefetch_related("friends").first() Pay the query cost just once.. instead of on every iteration of the loop!

ThePadawan 6y ago
Yep, that was pretty much exactly the issue. Except this was in a home-rolled solution which made it incredibly opaque where the ".friends" access actually happened (or if it wasn't ".acquaintances.where(type='friend')". Made it very easy to get pages on the road, which just turned incredibly slow later down that same road, and fixing it wasn't trivial without knowing exactly what to preload when.
josalhor 6y ago
I currently have a project with Django and you made me look this up because I remembered it differently. And yes, there is in fact a cache layer between the QuerySet (.all) and the iteration. See: https://docs.djangoproject.com/en/3.1/topics/db/queries/#caching-and-querysets https://docs.djangoproject.com/en/3.1/topics/db/queries/#cac...
olavgg 6y ago
With Hibernate, you have something called "open session in view" that should always be disabled to prevent bad practices like this. I really hope there is something similar for Django, as I may have to rewrite a Spring Boot application to Django(politics and lack of java resources).
gedazz 6y ago
I have never encountered this issue in Django. Perhaps your post lacks some context. Is `friends` a custom method/property? Asking because I'm used to seeing attributes like `friends_set`. The only scenario I see where your example would need `prefetch_related` is if your snippet looked something like this: {% for user in all_users %} <ul> {% for friend in user.friends.all %} <li>{{ friend.name }}</li> {% endfor %} </ul> {% endfor %} To avoid the classic N+1 query problem, all_users must be loaded like so: User.objects.all().prefetch_related("friends") If you're writing verbose expressions such as this in Django: User.objects.filter(pk=<pk>).prefetch_related("friends").first() You must be doing something wrong.
- Izkata 6y ago
  Based on context, GP's example is a ManyToMany field. "friend_set" is what you'd get on a reverse ForeignKey or reverse ManyToMany by default, but can be changed with the "related_name" kwarg. Either way you're right, GP's example as given doesn't have the 1+N problem (it's 1+1 at worst, a single query for user and a second single query for all friends), there's definitely missing context if they are seeing it.
berkes 6y ago
This is very similar in Rails. Only in Rails it is far easier to accidentally introduce interfaces that do this: the downside of having a very easy DSL to quickly add relations between records/tables is that people very quickly add relations between records/tables: introduce spaghetti. And in Rails it is very hard to debug, because ActiveRecord tries hard to be smart, but this "smart" quickly degrades into "black magic" when stuff gets complex (mostly caused by: see above, in my experience). Edit: currently working on a codebase where 3000ms queries to get 200 items is no exception, where 500+ queries to build a page is common and where, if you change a foo on a bar, suddenly at the other end of the app, your blargs and blazzes start blurping out SQL that used to run 200ms but now adds some wheres and limits and group-bys that make it run over 2000ms. Yay for Rails trying to help!/s
- dwheeler 6y ago
  You might check out the gem "fasterer" which warns about n+1 queries.
  - berkes 6y ago
    We use bullit, and scoutAPM. So we are well aware of N+1 queries. Edit: but thanks for the suggestion. Will have a look if it works better than bullit. There's a difference, however, between being aware of them and fixing them. Especially when they were caused by an unrelated change; when you cannot just roll back that change.
jordiburgos 6y ago
Why that is not already done by the framework? 99.9% of the people using that for look would benefit of having it in 1 query instead of N queries.
- pedrovhb 6y ago
  Because that's extra work done by the DB for no reason if you don't intend on using the Friends field (say, you're only getting user data to render the "Welcome, {{username}}" text).

ThePadawan 6y ago

Yep, that was pretty much exactly the issue. Except this was in a home-rolled solution which made it incredibly opaque where the ".friends" access actually happened (or if it wasn't ".acquaintances.where(type='friend')". Made it very easy to get pages on the road, which just turned incredibly slow later down that same road, and fixing it wasn't trivial without knowing exactly what to preload when.

josalhor 6y ago

I currently have a project with Django and you made me look this up because I remembered it differently. And yes, there is in fact a cache layer between the QuerySet (.all) and the iteration. See: https://docs.djangoproject.com/en/3.1/topics/db/queries/#caching-and-querysets https://docs.djangoproject.com/en/3.1/topics/db/queries/#cac...

olavgg 6y ago

With Hibernate, you have something called "open session in view" that should always be disabled to prevent bad practices like this. I really hope there is something similar for Django, as I may have to rewrite a Spring Boot application to Django(politics and lack of java resources).

gedazz 6y ago

I have never encountered this issue in Django. Perhaps your post lacks some context. Is `friends` a custom method/property? Asking because I'm used to seeing attributes like `friends_set`. The only scenario I see where your example would need `prefetch_related` is if your snippet looked something like this: {% for user in all_users %} <ul> {% for friend in user.friends.all %} <li>{{ friend.name }}</li> {% endfor %} </ul> {% endfor %} To avoid the classic N+1 query problem, all_users must be loaded like so: User.objects.all().prefetch_related("friends") If you're writing verbose expressions such as this in Django: User.objects.filter(pk=<pk>).prefetch_related("friends").first() You must be doing something wrong.

Izkata 6y ago
Based on context, GP's example is a ManyToMany field. "friend_set" is what you'd get on a reverse ForeignKey or reverse ManyToMany by default, but can be changed with the "related_name" kwarg. Either way you're right, GP's example as given doesn't have the 1+N problem (it's 1+1 at worst, a single query for user and a second single query for all friends), there's definitely missing context if they are seeing it.

berkes 6y ago

This is very similar in Rails. Only in Rails it is far easier to accidentally introduce interfaces that do this: the downside of having a very easy DSL to quickly add relations between records/tables is that people very quickly add relations between records/tables: introduce spaghetti. And in Rails it is very hard to debug, because ActiveRecord tries hard to be smart, but this "smart" quickly degrades into "black magic" when stuff gets complex (mostly caused by: see above, in my experience). Edit: currently working on a codebase where 3000ms queries to get 200 items is no exception, where 500+ queries to build a page is common and where, if you change a foo on a bar, suddenly at the other end of the app, your blargs and blazzes start blurping out SQL that used to run 200ms but now adds some wheres and limits and group-bys that make it run over 2000ms. Yay for Rails trying to help!/s

dwheeler 6y ago
You might check out the gem "fasterer" which warns about n+1 queries.
- berkes 6y ago
  We use bullit, and scoutAPM. So we are well aware of N+1 queries. Edit: but thanks for the suggestion. Will have a look if it works better than bullit. There's a difference, however, between being aware of them and fixing them. Especially when they were caused by an unrelated change; when you cannot just roll back that change.

jordiburgos 6y ago

Why that is not already done by the framework? 99.9% of the people using that for look would benefit of having it in 1 query instead of N queries.

pedrovhb 6y ago
Because that's extra work done by the DB for no reason if you don't intend on using the Friends field (say, you're only getting user data to render the "Welcome, {{username}}" text).