There is a case where having N+1 queries are beneficial. In Rails terms, it's when you perform Russian doll caching, but you can do this in any framework. The idea is you can cache a specific X thing which might make a query to an associated Y thing. A textbook N+1 query case (ie. a list of posts (X) that get the author's name (Y)). If you render the view without any cache with 10 things then you'd perform 20 queries…
Now you have like three problems instead of one - N+1 queries in the cold-cache case is slow, cache invalidation when something changes, and much more overall complexity...
Ban 1+N in Django
71–80 of 153 posts
Re: Ban 1+N in Django
#72There is a case where having N+1 queries are beneficial. In Rails terms, it's when you perform Russian doll caching, but you can do this in any framework. The idea is you can cache a specific X thing which might make a query to an associated Y thing. A textbook N+1 query case (ie. a list of posts (X) that get the author's name (Y)). If you render the view without any cache with 10 things then you'd perform 20 queries…
You'd ideally want to do something like dataloader, where you look up your N Xs in a single cache query, and then do a single database lookup for the (N-C) Xs that weren't in cache. You can then either eagerly load the Ys with the Xs like you said, or do a secondary cache lookup for every Y, and potentially another single database query for the Ys not in cache. Unfortunately this pattern gets really hairy if you're n…
Once you're there a microservice has some advantages. Wrap a cache with a service, implement multi-get, anything not in the cache calls through to the database.
Re: Ban 1+N in Django
#73There is a case where having N+1 queries are beneficial. In Rails terms, it's when you perform Russian doll caching, but you can do this in any framework. The idea is you can cache a specific X thing which might make a query to an associated Y thing. A textbook N+1 query case (ie. a list of posts (X) that get the author's name (Y)). If you render the view without any cache with 10 things then you'd perform 20 queries…
Now you have like three problems instead of one - N+1 queries in the cold-cache case is slow, cache invalidation when something changes, and much more overall complexity...
Re: Ban 1+N in Django
#74This 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.
> Writing SQL is not that hard, and mapping the results to a type isn’t that hard either. But... Then you've written an ORM.
Re: Ban 1+N in Django
#75I usually add it to existing integration tests so that they raise exceptions on N+1. If test coverage is low then I would suggest sending the N+1 metrics to something like datadog. That way your users using the product will reveal all the N+1 issues on your monitoring solution.
EDIT: I should add a screenshot to the README lol but the middleware will print each api being called with a nice table showing each query and the number of times it was called for the api.
Re: Ban 1+N in Django
#76If you're going to do this, you may as well simply not use an ORM. Which is definitely the solution that I'd recommend; they are just not actually a good idea.
The ORM provides a myriad other features, like adapters for every production database under the sun, query composition that is literally impossible in plain SQL, a reasonable interface to the admin and the ecosystem of Django apps, and above all: a logical interface that maps _business objects_ to their SQL tables. The ORM hate seems to come from people whose day to day interaction with data tables isn't mediated by…
Re: Ban 1+N in Django
#77I 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…
Re: Ban 1+N in Django
#78Earlier quoted context omitted.
The ORM provides a myriad other features, like adapters for every production database under the sun, query composition that is literally impossible in plain SQL, a reasonable interface to the admin and the ecosystem of Django apps, and above all: a logical interface that maps _business objects_ to their SQL tables. The ORM hate seems to come from people whose day to day interaction with data tables isn't mediated by…
... Or by people that actually understand the impedance mismatch between objects and data (quick django example - request data and models are different and not easily interchangeable). Or people that require good caching implementations. Or people that actually design database systems schema-first. Or peoplw that rely on advanced usage that isnt always easy to perform in orm's. The list goes on.
function action_endpoint():
if request.is_valid():
data = request.to_data_object(data_object_class)
self.service.update(data)
return success()
return request.errors()
in this simple example, the internal data representation isn't using a full-blown object, but a "data object" (ex. a dataclass). There are no transitive database dependencies, it behaves just like a fancy dict. When including data_object_class, I'm not including the whole database driver. When passing this to other system components, this can be serialized & de-serialized because it has no intrinsic behavior implemented.
As such, when using architectural patterns like three-tier design or hexagonal design, you can pass data between layers without any "external"(from a data perspective) dependency; this allows the frontend to be completely agnostic on where & how data is stored. In fact, in this example, self.service could be an RPC proxy object to another subsystem, in a different server. The advantage of this design becomes quite apparent when you need to decouple how data is stored from how data is processed - you start designing your application in a service-oriented approach, instead of a model-oriented approach.In fact, one could just create an endpoint processor that receives a table name, and infers the rest of the logic in the middle (the request validation, the data object, the glue service for database), that today can write to a database, and tomorrow just calls an api without rebuilding your application.
Re: Ban 1+N in Django
#79Rails has Bullet[0] to help identify and warn you against N+1 Does Django have anything active? Quick search revealed nplusone[1] but its been dead since 2018. [0] https://github.com/flyerhzm/bullet [1] https://github.com/jmcarp/nplusone
Re: Ban 1+N in Django
#80There is a case where having N+1 queries are beneficial. In Rails terms, it's when you perform Russian doll caching, but you can do this in any framework. The idea is you can cache a specific X thing which might make a query to an associated Y thing. A textbook N+1 query case (ie. a list of posts (X) that get the author's name (Y)). If you render the view without any cache with 10 things then you'd perform 20 queries…
Now you have like three problems instead of one - N+1 queries in the cold-cache case is slow, cache invalidation when something changes, and much more overall complexity...