Reducing db requests in django

Question

I have a view that searches through a database of movie clips and converts and returns the results in the same way -

# From the following results: Avatar - James Cameron - director Avatar - James Cameron - writer Avatar - James Cameron - editor Avatar - Julie Jones - writer Crash - John Smith - director # ...display in the template as: Avatar - James Cameron (director, writer, editor) Avatar - Julie Jones (writer) Crash - John Smith (director) 

However, when I do this conversion and do print connection.queries , I get to the database about 100 times. Here is what I have now -

 # in models class VideoCredit(models.Model): video = models.ForeignKey(VideoInfo) # if the credit is a current user, FK to his profile, profile = models.ForeignKey('UserProfile', blank=True, null=True) # else, just add his name name = models.CharField(max_length=100, blank=True) # normalize name for easier searching / pulling of name normalized_name = models.CharField(max_length=100) position = models.ForeignKey(Position) timestamp = models.DateTimeField(auto_now_add=True) actor_role = models.CharField(max_length=50, blank=True) class VideoInfo(models.Model): title = models.CharField(max_length=256, blank=True) uploaded_by = models.ForeignKey('UserProfile') ... 

class Position (models.Model): position = models.CharField (max_length = 100) ordering = models.IntegerField (max_length = 3)

 class UserProfile(models.Model): user = models.ForeignKey(User, unique=True) ... 

In my opinion, I am building a list of three tuples in the form (name, video, [list_of_positions]) to display credits -

  credit_set = VideoCredit.objects.filter(***depends on a previous function***) list_of_credit_tuples = [] checklist = [] # I am creating a 'checklist' to see whether to append the positions # list of create a new tuple entry for credit in credit_set: if credit.profile: # check to see if the credit has an associated profile name = credit.profile else: name = credit.normalized_name if (credit.normalized_name, credit.video) in checklist: list_of_keys = [(name, video) for name, video, positions in list_of_credit_tuples] index = list_of_keys.index((name, credit.video)) list_of_credit_tuples[index][2].append(credit.position) else: list_of_credit_tuples.append((name, credit.video, [credit.position])) checklist.append((credit.normalized_name, credit.video)) ... 

And finally, in my template for displaying loans (note: if the loan has a profile, specify a link to the user profile) -

 {% for name, video, positions in list_of_credit_tuples %} <p>{% if name.full_name %} <a href="{% url profile_main user_id=name.id %}">{{name.full_name}}</a> {% else %} {{name}} {% endif %} <a href="{% url videoplayer video_id=video.id %}">{{video}}</a> ({% for position in positions %}{% ifchanged %}{{position}}{% endifchanged %}{% if not forloop.last %}, {% endif %}{% endfor %}) {% endfor %} 

Why and where does this view create so many db queries? How and how could I make this presentation function more efficient / better? Thanks.

Answer 1

You want to look in select_related () ( https://docs.djangoproject.com/en/1.3/ref/models/querysets/#select-related ) to solve the query leak problem. If you know in advance, you'll look at the data about the foreignkey-related models that you want to add select_related. Even better, if you know that these are just a couple of additional keys, you can only add the ones you need.

Anytime django launches a huge number of requests more than you expected, select_related is almost always the correct answer

Answer 2

Try adding this Django snippet that returns the number of requests, as well as the requests themselves to your template:

http://djangosnippets.org/snippets/159/

This will easily tell you where the leak is coming from.