Django - queries made repeat/inefficient

Question

Alright, I have a Django view, like this:

@render_to('home/main.html')
def login(request):
    # also tried Client.objects.select_related().all()
    clients = Client.objects.all()
    return {'clients':clients}

And I have a template, main.html, like this:

<ul>
{% for client in clients %}
<li>{{ client.full_name }}</li>
    <ul>
    {% for pet in client.pets.all %}
        <li>{{ pet.full_name }}</li>
    {% endfor %}
    </ul>
{% endfor %}
</ul>

I also print out all the queries in sql_queries at the bottom of my base template. When I run this view, the following queries are made:

SELECT `home_client`.`id`, ... FROM `home_client`;
SELECT `home_pet`.`id`, ... FROM `home_pet` WHERE `home_pet`.`client_id` = 1;
SELECT `home_client`.`id`, ... FROM `home_client` WHERE `home_client`.`id` = 1;
SELECT `home_client`.`id`, ... FROM `home_client` WHERE `home_client`.`id` = 1;
SELECT `home_pet`.`id`, ... FROM `home_pet` WHERE `home_pet`.`client_id` = 2;
SELECT `home_client`.`id`, ... FROM `home_client` WHERE `home_client`.`id` = 2;

My question is, why are all these queries being made? Shouldn't it just be 1 query to retrieve all the clients and a query per client to retrieve all the pets from each client? I have 2 clients in the home_client table, so it should be 3 queries total. Most troubling of all is that queries 3 and 4 are 100% identical. I don't want to "prematurely optimize" or anything but I do want to make sure Django isn't being wildly inefficient. Any help on this would be appreciated. Thanks.

Answer 1

Django uses a cache. The RDBMS uses a cache. Don't prematurely optimize the queries.

You can play with bulk queries in your view function instead of one-at-a-time queries in your template.

@render_to('home/main.html')
def login(request):
    # Query all clients 
    clients = Client.objects.all()
    # Assemble an in-memory table of pets
    pets = collections.defaultdict(list)
    for p in Pet.objects.all():
        pets[pet.client].append(p)
    # Create clients and pets tuples
    clientsPetTuples = [ (c,pets[c]) for c in clients ]
    return {'clientPets': clientsPetTuples}

However, you don't seem to have any evidence that your template is the slowest part of your application.

Further, this trades off giant memory use against SQL use. Until you have measurements that prove that your template queries are actually slow, you shouldn't be over thinking the SQL.

Don't worry about the SQL until you have evidence.

Answer 2

try using Client.objects.all().select_related()

This will automagically also cache related models in a single database query.

Answer 3

Does Client 1 have 2 Pets and Client 2 have 1 Pet?

If so, that would indicate to me that Pet.full_name or something else you're doing in the Pet display loop is trying to access its related Client's details. Django's ORM doesn't use an identity map, so accessing the Client foreign key from any of your Pet objects would require hitting the database again to retrieve that Client.

P.S. select_related won't have any effect on the data you're using in this scenario as it only follows foreign-key relationships, but the pet-to-client relationship is many-to-one.

Update: if you want to avoid having to change the logic in Pet.full_name or having to perform said logic in the template instead for this case, you could alter the way you get a handle on each Client's Pets in order to prefill the ForeignKey cache with for each Pet with its Client:

class Client(models.Model):
    # ...
    def get_pets(self):
        for pet in self.pets.all():
            setattr(pet, '_client_cache', self)
            yield pet

...where the 'client' part of '_client_cache' is whatever attribute name is used in the Pet class for the ForeignKey to the Pet's Client. This takes advantage of the way Django implements access to ForeignKey-related objects using its SingleRelatedObjectDescriptor class, which looks for this cache attribute before querying the database.

Resulting template usage:

{% for pet in client.get_pets %}
...
{% endfor %}