Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ban 1+N in Django

suor.github.io

Post reply on HN
Print view View original

11–20 of 153 posts

Re: Ban 1+N in Django

#11

Post by zdragnar »

nickjj wrote: post #7

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…

That's great if you can fit a lot of your database in your server's memory, but seems like a terrible headache once you get a decent number of users.

Personally, I'd much rather have sane queries in the first place, but rails isn't really my cup of tea either, so take my opinion with a large pinch of salt if you do.

Re: Ban 1+N in Django

#13

Post by tveita »

nickjj wrote: post #7

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…

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 not using promises and an event loop.

https://www.npmjs.com/package/dataloader

Re: Ban 1+N in Django

#14

Post by spapas82 »

This 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.

Re: Ban 1+N in Django

#16

Post by binarymax »

This 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.

Re: Ban 1+N in Django

#17

Post by Kamq »

zdragnar wrote: post #11
nickjj wrote: post #7

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…

That's great if you can fit a lot of your database in your server's memory, but seems like a terrible headache once you get a decent number of users. Personally, I'd much rather have sane queries in the first place, but rails isn't really my cup of tea either, so take my opinion with a large pinch of salt if you do.

> That's great if you can fit a lot of your database in your server's memory, but seems like a terrible headache once you get a decent number of users.

You'd surely care about getting a significant chunk of your usage in server memory rather than what percentage of total data that is, no?

To take the site we're on as an example, I'd be willing to bet the 30 things on the front page have one or two orders of magnitude more traffic than anything else (and probably a few more orders of magnitude more than the median post).

Re: Ban 1+N in Django

#18

Post by btown »

My Chaotic Good take on this: one could implement a qs.auto_fetch_deferred() that emits model instances with weak back-references to a WeakSet of all instances emitted, and on a deferred get on ANY instance, it prefetches that attribute onto ALL of the instances... so that it doesn't just complain, but actually fixes your 1+N issue. But here lies absolute madness...

Re: Ban 1+N in Django

#19

Post by mattbillenstein »

binarymax wrote: post #16

This 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.

You're on the right side of the bell-curve meme my friend, but there are a lot more people in the thick-framework camp who spend their days getting lost in the complexity of ORMs and related tech...

Re: Ban 1+N in Django

#20

Post by Waterluvian »

On the topic of accidentally doing lots of extra queries, I love using Model.objects.raw to control exactly how it’s retrieving model data. I love how it keeps the results inside the Model realm but gives you careful control.

I also like that if you access fields you didn’t ask for, it’ll go get them. But I wish it screamed louder when this happened. “Your logic works but we had to do extra queries. This might be an error!” So the featured article is incredibly valuable. This ought to be built-in.

Django Silk has been critical for discovering these cases. They’re too easy to do.

I love the middle ground of “ORM but you write the SQL.”

Post reply on HN