4 ms·

> The Appropriate Uses For SQLite page says that dynamic pages on the SQLite website typically do about 200 SQL statements each. This has provoked criticism fro

> The Appropriate Uses For SQLite page says that dynamic pages on the SQLite website typically do about 200 SQL statements each. This has provoked criticism from readers. Examples: > "200 SQL statements is a ridiculously high number for a single page" > "For most sites, 200 queries is way, way, way too much." > "[This is] bad design" Funnily enough, at a recent workplace the guideline was "if it only does 200 queries, you've optimized it enough".

PurpleFoxy 6y ago
I can relate to this. It’s so easy to write a some code that fires off 10 queries but it’s obscured by the ORM and then it’s in an index listing which isn’t paginated and eventually some user does things weird and it ends up with 1000 items so this one listing runs 10,000 queries.
- TeMPOraL 6y ago
  In my experience, it doesn't take the user doing anything weird - all it takes is to use ORM enough in the project, and suddenly you have hundreds of separate queries being run to fetch something trivial, which could be done in one or two queries of hand-written SQL.
  - matthewmacleod 6y ago
    In my experience, those "one or two queries of hand-written SQL" quickly balloon out of control into a litany of crimes, because the assumption they will be better relies on having the kind of developer who can easily write "one or two queries of hand-written SQL" but are somehow incapable of reading the documentation or using an ORM correctly.
    - wtetzner 6y ago
      Writing SQL is so much easier than understanding ORMs. ORMs tend to be very complex, because the problem they're trying to solve requires that complexity. Now maybe my perspective is biased too much, since the only ORM I've really used is Hibernate. But in that experience, I learned that Hibernate brings a ton of complexity with no real value. Seems like a high price to pay to avoid learning SQL. The sweet spot seems to be using a library like JOOQ, which lets you write queries, as well as safely construct queries at runtime.
VWWHFSfQ 6y ago
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.
Jiocus 6y ago
> "200 SQL statements is a ridiculously high number for a single page" > "For most sites, 200 queries is way, way, way too much." > "[This is] bad design" Now, if we read these statements again, now as excited enthusiasm expressed by a developer in awe of this rule-breaking architecture of web development, enabled by SQLite. The submission really provides a practical example of the developers dogfooding SQLite, and designs that could be unpractical with other tools. Great Support your local
72deluxe 6y ago
Just imagine the horror those using Magento would get when they see how many queries it runs for doing anything at all!

PurpleFoxy 6y ago

I can relate to this. It’s so easy to write a some code that fires off 10 queries but it’s obscured by the ORM and then it’s in an index listing which isn’t paginated and eventually some user does things weird and it ends up with 1000 items so this one listing runs 10,000 queries.

TeMPOraL 6y ago
In my experience, it doesn't take the user doing anything weird - all it takes is to use ORM enough in the project, and suddenly you have hundreds of separate queries being run to fetch something trivial, which could be done in one or two queries of hand-written SQL.
- matthewmacleod 6y ago
  In my experience, those "one or two queries of hand-written SQL" quickly balloon out of control into a litany of crimes, because the assumption they will be better relies on having the kind of developer who can easily write "one or two queries of hand-written SQL" but are somehow incapable of reading the documentation or using an ORM correctly.
  - wtetzner 6y ago
    Writing SQL is so much easier than understanding ORMs. ORMs tend to be very complex, because the problem they're trying to solve requires that complexity. Now maybe my perspective is biased too much, since the only ORM I've really used is Hibernate. But in that experience, I learned that Hibernate brings a ton of complexity with no real value. Seems like a high price to pay to avoid learning SQL. The sweet spot seems to be using a library like JOOQ, which lets you write queries, as well as safely construct queries at runtime.

VWWHFSfQ 6y ago

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.

Jiocus 6y ago

> "200 SQL statements is a ridiculously high number for a single page" > "For most sites, 200 queries is way, way, way too much." > "[This is] bad design" Now, if we read these statements again, now as excited enthusiasm expressed by a developer in awe of this rule-breaking architecture of web development, enabled by SQLite. The submission really provides a practical example of the developers dogfooding SQLite, and designs that could be unpractical with other tools. Great Support your local

72deluxe 6y ago

Just imagine the horror those using Magento would get when they see how many queries it runs for doing anything at all!