How to reduce DB queries?

Question

Models:

class Technology(models.Model):
    name = models.CharField(max_length=100, unique=True)
    slug = models.SlugField(max_length=100, unique=True)

class Site(models.Model):
    name = models.CharField(max_length=100, unique=True)
    slug = models.SlugField(max_length=100, unique=True)
    technology = models.ManyToManyField(Technology, blank=True, null=True)

Views:

def portfolio(request, page=1):
    sites_list = Site.objects.select_related('technology').only('technology__name', 'name', 'slug',)
    return render_to_response('portfolio.html', {'sites':sites_list,}, context_instance=RequestContext(request))

Template:

{% for site in sites %}
<div>
    {{ site.name }},
    {% for tech in site.technology.all %}
        {{ tech.name }}
    {% endfor %}
</div>
{% endfor %}

But in that example each site makes 1 additional query to get technology list. Is there any way to make it in 1 query somehow?

Do you want to generate a SQL join in the end? Django's way to generate joins is documented here, but I guess you've already read it. — Bastien Léonard
– Bastien Léonard, Commented Feb 27, 2011 at 14:41
In template in main loop I want to display all technologies assigned to the each site. Technologies are assigned by m2m relation. So I run subloop as You can see in code above. I want to reduce the number of queries executed by those subqueries. I want to know, if there is a better way to do this. — robos85
– robos85, Commented Feb 27, 2011 at 15:24

Dick · Accepted Answer · 2011-02-28 15:35:08Z

1

What you are looking for is an efficient way to do reverse foreign-key lookups. A generic approach is:

qs = MyRelatedObject.objects.all()
obj_dict = dict([(obj.id, obj) for obj in qs])
objects = MyObject.objects.filter(myrelatedobj__in=qs)
relation_dict = {}
for obj in objects:
    relation_dict.setdefault(obj.myobject_id, []).append(obj)
for id, related_items in relation_dict.items():
    obj_dict[id].related_items = related_items

I wrote a blogpost about this a while ago, you can find more info here: http://bit.ly/ge59D2

answered Feb 28, 2011 at 15:35

Dick

1,23813 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

robos85 Over a year ago

What if I have m2m not ForeignKey?

Dick Over a year ago

Sorry, I overlooked the m2m relationship in your code. I haven't tested this but what about: 1) Loading the intermediate table as a qs (I think this is only possible if you specify a custom intermediate table), 2) Loading 2 more qs from both sides of the m2m relationship with (myrelatedobj__in=qs), and then joining the 2 by hand? It shouldn't take more than 3 queries.

Kamran Khan · Accepted Answer · 2011-02-27 14:10:01Z

0

How about:

Using Django's session framework; load list request.session['lstTechnology'] = listOfTechnology on startup. And use session in rest of the app.

answered Feb 27, 2011 at 14:10

Kamran Khan

9,9842 gold badges33 silver badges41 bronze badges

1 Comment

robos85 Over a year ago

Yhm, so there is no way to reduce it? Another way is cache but I'm wondering if there's way to do it somehow.

Collectives™ on Stack Overflow

How to reduce DB queries?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related