I was working on a library to do this which I never got around to fully finishing but the idea is there: https://github.com/lime-green/django-orm-plus I think all I had left was polishing the auto add logic and testing with a real project
Ban 1+N in Django
51–60 of 153 posts
Re: Ban 1+N in Django
#52There is a problem with the URL. I think this is the correct one https://suor.github.io/blog/2023/03/26/ban-1-plus-n-in-djang...
[deleted]
Re: Ban 1+N in Django
#53This raises a question, are there any easy batching library in Python? It's like N+1 problem but for non-SQL.
I have various RPCs, some of them accept multi actions with certain limits so how to write non-batch calls in code but execute them as a whole as needed?
Re: Ban 1+N in Django
#54I wonder why ORMs still(?) work as simple wrappers and never track access patterns. If you see that `in books` generator’s results experience accesses through a relationship, it’s pretty obvious to join it in advance after few misses and serve `book.author.full_name` from cache.
Of course that would make ORM more complex, but why would you need one otherwise. A good database interface should make good guesses, probably with some hints that could be slapped over without changing naive code patterns. But instead we get 1000 different cool ways to create a table and select a row by id.
Re: Ban 1+N in Django
#55This is why I always advocated against ORMs. It’s so easy to fall into traps like this without even knowing it, and while you can work around it in some ORMs it is not obvious. Writing SQL is not that hard, and mapping the results to a type isn’t that hard either. So with an ORM you might end up saving several hours of work up front for lots of pain later.
It's extremely easy to write SQL queries that take a lot longer than they look like - an unindexed full table scan looks exactly the same as an indexed join, for example, whereas in a good ORM they will look different. So as far as I can see writing SQL manually is just extra drudgery for no real gain.
I don't know.
Hand-written SQL means a bunch of work and you have to know SQL. But it is simple and transparent.
ORMs automate a lot of simple and repetitive SQL, but to use them effectively you really have to know SQL extremely well and understand the ORM deeply as well.
So I guess it depends on what you are doing. ORMs can be useful but they require a lot more knowledge to use effectively than hand-coded SQL does.
Re: Ban 1+N in Django
#56This is very useful! I'm gonna start integrating it with my projects. However having a way to allow/not allow n+1 queries (like a context manager) would be much better. The thing is that there are times where n+1 isn't a big problem and fixing it would be a form of premature optimisation. I'd prefer to be in control and decide if I care about the n+1 query situation or not for some specific view.
I agree that in most cases this will amount to premature optimisation. Most django sites will forever remain small, and there's a lot of benefit to a simple, intuitive (if inefficient) ORM. Namely; that's it's easy for a jr developer to get in, and get results. (That's why we're coding in python, yes?)
Naturally yes, there's a certain scale where N+1 starts to really matter. And in those situations it might be helpful to be able to enable some feature to warn (or error) on N+1. But optimizing your code to avoid run in fewer queries is often (in cases where prefetch_related doesn't cut it) non-trivial. And presumably when you get to this scale, you're also code-reviewing the jr developers.. so I'm not sure how urgent it is that we all "Ban N+1 in Django" as a rule.
Re: Ban 1+N in Django
#57Earlier quoted context omitted.
It's extremely easy to write SQL queries that take a lot longer than they look like - an unindexed full table scan looks exactly the same as an indexed join, for example, whereas in a good ORM they will look different. So as far as I can see writing SQL manually is just extra drudgery for no real gain.
I don't know. Hand-written SQL means a bunch of work and you have to know SQL. But it is simple and transparent. ORMs automate a lot of simple and repetitive SQL, but to use them effectively you really have to know SQL extremely well and understand the ORM deeply as well. So I guess it depends on what you are doing. ORMs can be useful but they require a lot more knowledge to use effectively than hand-coded SQL does.
> ORMs automate a lot of simple and repetitive SQL, but to use them effectively you really have to know SQL extremely well and understand the ORM deeply as well.
I'm not sure. You need to know the relational model pretty well, but you don't have to remember the zillions of quirks and edge cases or differences between dialects that SQL has. IME that's what takes most of the memorization effort.
Re: Ban 1+N in Django
#58I wonder why ORMs still(?) work as simple wrappers and never track access patterns. If you see that `in books` generator’s results experience accesses through a relationship, it’s pretty obvious to join it in advance after few misses and serve `book.author.full_name` from cache. Of course that would make ORM more complex, but why would you need one otherwise. A good database interface should make good guesses, probab…
Because people want their queries to have predictable performance.
Re: Ban 1+N in Django
#59Heh, I'm literally in the middle of optimizing some N+1 query endpoints in a Django application for work, made a bit more tricky because of DRF's serializer.
I think a setting for lazy queries would be a good solution, with it enabled by default to ease the transition. It would be nice to have a couple options, though – allow, warn, and error.
It would also be great to have a way to change the setting on the fly so that, e.g., the Django shell can automatically enable it for those quick debugging sessions.
Re: Ban 1+N in Django
#60This is why I always advocated against ORMs. It’s so easy to fall into traps like this without even knowing it, and while you can work around it in some ORMs it is not obvious. Writing SQL is not that hard, and mapping the results to a type isn’t that hard either. So with an ORM you might end up saving several hours of work up front for lots of pain later.
It's extremely easy to write SQL queries that take a lot longer than they look like - an unindexed full table scan looks exactly the same as an indexed join, for example, whereas in a good ORM they will look different. So as far as I can see writing SQL manually is just extra drudgery for no real gain.
> an unindexed full table scan looks exactly the same as an indexed join, for example, whereas in a good ORM they will look different
ORM queries compile down to SQL, so how would they look different?