Electronics & Programming

develissimo

Open Source electronics development and programming

  • You are not logged in.

#1 Jan. 12, 2011 15:32:44

Matt H.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Help optimising database calls


Hello,
I'm new to django, and set up a website recently, (puncut.com). I was hoping
someone might be able to explain how to optimise database calls because at
the moment some pages are taking 5 seconds before django responds.
I'll summarise the scenario below:

# models
class Thread(models.Model) :
... bunch of fields including title, description, author (linked to User)

class Pun(models.Model) :
thread = models.ForeignKey(Thread)
author = models.ForeignKey(User)
... other fields like content etc.

class Like(models.Model) :
liker = models.ForeignKey(User)
pun = models.ForeignKey(Pun)

So there are puns in threads, and users can like puns.
The view which lists latest threads is particularly slow. It sends threads
to the template, and the template loops through threads and inside it loops
through the puns which are in that thread. in pseudo code:
for thread in threads
for pun in thread.pun_set
output pun.content
But I reckon that makes way to many calls to the database. I think I ought
to use select_related , but I tried it and it didn't speed it up.
Thanks for any help,
Matt


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

Offline

#2 Jan. 12, 2011 15:39:43

Dan F.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Help optimising database calls


> Hello,
> I'm new to django, and set up a website recently, (puncut.com). I was hoping
> someone might be able to explain how to optimise database calls because at
> the moment some pages are taking 5 seconds before django responds.
> I'll summarise the scenario below:




> But I reckon that makes way to many calls to the database. I think I ought to
> use select_related , but I tried it and it didn't speed it up.

Check out the Django debug toolbar. It will show you exactly what SQL queries
are being run, and you'll be able to see a stack trace for each, indicating
what caused the query to happen. You will also be able to see the effect of any
select_related() calls you make.

Cheers,
Dan
--
Dan Fairs | dan.fa...@gmail.com | www.fezconsulting.com


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

Offline

#3 Jan. 12, 2011 15:53:18

Matias A.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Help optimising database calls


Try to reduce the queries on your view, select_related won't help a lot if
using Thread model, try to collect Pun values on your view first and group them
by thread, itertools.groupby could be handy in this case. Try something like:

threads = Thread.objects.select_related('user').filter(...)
thread_ids =
Pun.objects.select_related('thread', 'author').filter(thread__in=thread_ids)

Then group Pun by thread and use that on your template.

Makes sense?

Regards,
Matías

Excerpts from Matt Henderson's message of Wed Jan 12 13:32:37 -0200 2011:
> Hello,
> I'm new to django, and set up a website recently, (puncut.com). I was hoping
> someone might be able to explain how to optimise database calls because at
> the moment some pages are taking 5 seconds before django responds.
> I'll summarise the scenario below:
>
> # models
> class Thread(models.Model) :
> ... bunch of fields including title, description, author (linked to User)
>
> class Pun(models.Model) :
> thread = models.ForeignKey(Thread)
> author = models.ForeignKey(User)
> ... other fields like content etc.
>
> class Like(models.Model) :
> liker = models.ForeignKey(User)
> pun = models.ForeignKey(Pun)
>
> So there are puns in threads, and users can like puns.
> The view which lists latest threads is particularly slow. It sends threads
> to the template, and the template loops through threads and inside it loops
> through the puns which are in that thread. in pseudo code:
> for thread in threads
> for pun in thread.pun_set
> output pun.content
> But I reckon that makes way to many calls to the database. I think I ought
> to use select_related , but I tried it and it didn't speed it up.
> Thanks for any help,
> Matt
--
Matías Aguirre <matiasagui...@gmail.com>

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

Offline

#4 Jan. 12, 2011 15:56:17

Łukasz R.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Help optimising database calls


On 12 January 2011 16:32, Matt Henderson <matt...@gmail.com> wrote:
> Hello,
> I'm new to django, and set up a website recently, (puncut.com). I was hoping
> someone might be able to explain how to optimise database calls because at
> the moment some pages are taking 5 seconds before django responds.
> I'll summarise the scenario below:
> # models
> class Thread(models.Model) :
>   ... bunch of fields including title, description, author (linked to User)
>
> class Pun(models.Model) :
>   thread        =   models.ForeignKey(Thread)
>   author        =   models.ForeignKey(User)
>   ... other fields like content etc.
> class Like(models.Model) :
>   liker         =   models.ForeignKey(User)
>   pun           =   models.ForeignKey(Pun)
> So there are puns in threads, and users can like puns.
> The view which lists latest threads is particularly slow. It sends threads
> to the template, and the template loops through threads and inside it loops
> through the puns which are in that thread. in pseudo code:
> for thread in threads
>     for pun in thread.pun_set
>         output pun.content
> But I reckon that makes way to many calls to the database. I think I ought
> to use select_related , but I tried it and it didn't speed it up.
> Thanks for any help,
> Matt

select_related() won't help you as it only traverses relations
"forward" ( you could select all puns with their thread, but not the
other way). AFAIK, this limitation comes from SQL, not the ORM itself.
The solution is to make the join/group_by operation "by-hand":

theads = list(Thread.objects.all()) # Materialize the queryset
threads_map = {}
for t in threads:
threads_map = t
t.pun_collection =
for pun in Puns.objects.all(): # or if threads are limited:
filter(thread__in=threads)
t.pun_collection.append(pun)

# now ``threads`` contains a list of threads with assigned puns.
--
Łukasz Rekucki

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

Offline

#5 Jan. 12, 2011 16:23:44

Matt H.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Help optimising database calls


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'))
threads_map = {}
for t in threads:
threads_map = t
t.pun_collection =
for pun in Pun.objects.all().filter(thread__in=threads):
threads_map.pun_collection.append(pun)
tpaginator = Paginator(threads, 10)

And I changed the template to loop through thread in threads then pun in
thread.pun_collection.
But the page still takes about 5 seconds to load : www.puncut.com/latest2
Is there something I've done wrong?
M

On 12 January 2011 15:47, Matias Aguirre <matiasagui...@gmail.com> wrote:

> Try to reduce the queries on your view, select_related won't help a lot if
> using Thread model, try to collect Pun values on your view first and group
> them
> by thread, itertools.groupby could be handy in this case. Try something
> like:
>
> threads = Thread.objects.select_related('user').filter(...)
> thread_ids =
> Pun.objects.select_related('thread',
> 'author').filter(thread__in=thread_ids)
>
> Then group Pun by thread and use that on your template.
>
> Makes sense?
>
> Regards,
> Matías
>
> Excerpts from Matt Henderson's message of Wed Jan 12 13:32:37 -0200 2011:
> > Hello,
> > I'm new to django, and set up a website recently, (puncut.com). I was
> hoping
> > someone might be able to explain how to optimise database calls because
> at
> > the moment some pages are taking 5 seconds before django responds.
> > I'll summarise the scenario below:
> >
> > # models
> > class Thread(models.Model) :
> > ... bunch of fields including title, description, author (linked to
> User)
> >
> > class Pun(models.Model) :
> > thread = models.ForeignKey(Thread)
> > author = models.ForeignKey(User)
> > ... other fields like content etc.
> >
> > class Like(models.Model) :
> > liker = models.ForeignKey(User)
> > pun = models.ForeignKey(Pun)
> >
> > So there are puns in threads, and users can like puns.
> > The view which lists latest threads is particularly slow. It sends
> threads
> > to the template, and the template loops through threads and inside it
> loops
> > through the puns which are in that thread. in pseudo code:
> > for thread in threads
> > for pun in thread.pun_set
> > output pun.content
> > But I reckon that makes way to many calls to the database. I think I
> ought
> > to use select_related , but I tried it and it didn't speed it up.
> > Thanks for any help,
> > Matt
> --
> Matías Aguirre <matiasagui...@gmail.com>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-us...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com<django-users%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
>http://groups.google.com/group/django-users?hl=en.
>
>

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

Offline

#6 Jan. 13, 2011 01:06:21

Matt H.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

Help optimising database calls


I managed to improve the speed of the site by using memcached to cache the
rendered Thread views
M

On 12 January 2011 16:23, Matt Henderson <matt...@gmail.com> 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'))
> threads_map = {}
> for t in threads:
> threads_map = t
> t.pun_collection =
> for pun in Pun.objects.all().filter(thread__in=threads):
> threads_map.pun_collection.append(pun)
> tpaginator = Paginator(threads, 10)
>
> And I changed the template to loop through thread in threads then pun in
> thread.pun_collection.
> But the page still takes about 5 seconds to load : www.puncut.com/latest2
> Is there something I've done wrong?
> M
>
> On 12 January 2011 15:47, Matias Aguirre <matiasagui...@gmail.com> wrote:
>
>> Try to reduce the queries on your view, select_related won't help a lot if
>> using Thread model, try to collect Pun values on your view first and group
>> them
>> by thread, itertools.groupby could be handy in this case. Try something
>> like:
>>
>> threads = Thread.objects.select_related('user').filter(...)
>> thread_ids =
>> Pun.objects.select_related('thread',
>> 'author').filter(thread__in=thread_ids)
>>
>> Then group Pun by thread and use that on your template.
>>
>> Makes sense?
>>
>> Regards,
>> Matías
>>
>> Excerpts from Matt Henderson's message of Wed Jan 12 13:32:37 -0200 2011:
>> > Hello,
>> > I'm new to django, and set up a website recently, (puncut.com). I was
>> hoping
>> > someone might be able to explain how to optimise database calls because
>> at
>> > the moment some pages are taking 5 seconds before django responds.
>> > I'll summarise the scenario below:
>> >
>> > # models
>> > class Thread(models.Model) :
>> > ... bunch of fields including title, description, author (linked to
>> User)
>> >
>> > class Pun(models.Model) :
>> > thread = models.ForeignKey(Thread)
>> > author = models.ForeignKey(User)
>> > ... other fields like content etc.
>> >
>> > class Like(models.Model) :
>> > liker = models.ForeignKey(User)
>> > pun = models.ForeignKey(Pun)
>> >
>> > So there are puns in threads, and users can like puns.
>> > The view which lists latest threads is particularly slow. It sends
>> threads
>> > to the template, and the template loops through threads and inside it
>> loops
>> > through the puns which are in that thread. in pseudo code:
>> > for thread in threads
>> > for pun in thread.pun_set
>> > output pun.content
>> > But I reckon that makes way to many calls to the database. I think I
>> ought
>> > to use select_related , but I tried it and it didn't speed it up.
>> > Thanks for any help,
>> > Matt
>> --
>> Matías Aguirre <matiasagui...@gmail.com>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django users" group.
>> To post to this group, send email to django-us...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> django-users+unsubscr...@googlegroups.com<django-users%2bunsubscr...@googlegroups.com>
>> .
>> For more options, visit this group at
>>http://groups.google.com/group/django-users?hl=en.
>>
>>
>

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

Offline

Board footer

Moderator control

Enjoy the 17th of August
PoweredBy

The Forums are managed by develissimo stuff members, if you find any issues or misplaced content please help us to fix it. Thank you! Tell us via Contact Options
Leave a Message
Welcome to Develissimo Live Support