Re: Help optimising database calls

On Wednesday 12 January 2011 18:23:37 Matt Henderson wrote:
> Thanks Lukasz and Matias,
> I've implemented something along those lines in my view:
>
> threads =
list(Thread.objects.select_related('author').all().order_by('created'))[:150]

^^^

Key lies here.

You pull all Thread objects from database, create a list from them and do
standard python slicing.

Querysets do support slicing and that is more efficient. Also you can use
queryset to pass (not a list) which makes Django to create inner sql queries

> threads_map = {}
> for t in threads:
> threads_map[t.pk] = t
> t.pun_collection = []
> for pun in Pun.objects.all().filter(thread__in=threads):
^^^

All is unnecessary here since you're doing filtering anyway.

> threads_map[pun.thread_id].pun_collection.append(pun)
> tpaginator = Paginator(threads, 10)
>

So you should do something like:

threads = Thread.objects.select_related('author').order_by('-created')[:150]

threads_map = {}
for t in threads:
threads_map[t.pk] = t

and then pull puns from pun_set()

Yes, that will cause 151 database calls.

You can make it happen with 2 database calls:

# First pull in thread ids with very lightweight call
thread_ids = Thread.objects.order_by('-created).values_list('pk', flat=True)
[:150]

And then,do the magic:

puns_with_threads =
Puns.objects.select_related('thread').filter(thread__in=thread_ids).order_by('thread__pk',
'-thread__created')

Now you can push puns with threads to view and use ifchanged tag

Or you can do all in one database go, this is not always efficient:

# This queryset is never evaluated as a standalone.
thread_qs = Thread.objects.order_by('-created)[:150]

And then,do the magic:

puns_with_threads =
Puns.objects.select_related('thread').filter(thread=thread_qs).order_by('thread__pk',
'-thread__created')

Template would look something like:

{% for pun in puns_with_threads %}
{% ifchanged pun.thread %}
Thread changed, put titles etc. here
{% endifchanged %}
Put pun data here.
{ %endfor %}

[snip snip snip - lot of models and other discussion]

--

Jani Tiainen

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.