Django N+1 query solution

Question

I visited http://guides.rubyonrails.org/active_record_querying.html after talking with a peer regarding N+1 and the serious performance implications of bad DB queries.

ActiveRecord (Rails):

clients = Client.includes(:address).limit(10)

Where client's have addresses, and I intend to access them while looping through the clients, Rails provides includes to let it know to go ahead and add them to the query, which eliminates 9 queries right off the bat.

Django:

https://github.com/lilspikey/django-batch-select provides batch query support. Do you know of other libraries or tricks to achieve what Rails provides above, but in a less verbose manor (as in the rails example wherein just 19 chars fix N+1 and is very clear)? Also, does batch-select address the concern in the same way, or are these two different things?

BTW, I'm not asking about select_related, though it may seem to be the answer at first glance. I'm speaking of a situation where address has a forign key to client.

Scurrile

Answer 1

You can do it with prefetch_related since Django 1.4: https://docs.djangoproject.com/en/dev/ref/models/querysets/#prefetch-related

If you're using < 1.4, have a look at this module: https://github.com/ionelmc/django-prefetch

It claims to be more flexible than Django's prefetch_related. Verbose but works great.

Chiaroscuro

Answer 2

Unfortunately, Django's ORM as of yet has no way of doing this.

Fortunately, it is possible to do it in only 2 queries, with a bit of work done in Python.

clients = list(Client.objects.all()[:10])
addresses = dict((x.client_id, x) for x in
    Address.objects.filter(client__in=clients))
for client in clients:
  print client, addresses[client.id]

Parameter

thanks. BTW, will that create as efficient of a query (ie, Is Rails likely doing anything magical that provides better performance, or is theirs probably just syntactic sugar for the same thing)? Obviously it'd be better if everything didn't have to be evaluated to memory right away, but perhaps theirs is doing that as well. – Scurrile

I don't know. It is possible to perform the action in one query, but that requires a level of trickery that Django's ORM doesn't yet have. – Parameter

Answer 3

django-batch-select is supposed to provide an answer to this problem, though I haven't tried it out. Ignacio's answer above seems best to me.

Discriminatory