Improving performance of django DB query

Question

I'm using django/apache/sqlite3 and I have a django model that looks like this:

class Temp_entry(models.Model):
    dateTime = models.IntegerField() #datetime
    sensor = models.IntegerField()   # id of sensor
    temp = models.IntegerField()     # temp as temp in Kelvin * 100

I'm trying to get the last 300 Temp_entry items to place into a graph. I do that this way:

revOutsideTempHistory = Temp_entry.objects.filter(sensor=49).order_by('dateTime').reverse()[:300]

However, this query takes ~1 second. Is there a way to improve this? I've dug around and found that order_by is horrible inefficient, so I'm hoping that there is a viable alternative?

An alternative I thought of, but can't figure out how to implement, would be to run the query every 20 minutes and keep it cached, that would be acceptable too, as the data can be slightly stale with no ill effects.

Alexey Savanovich · Accepted Answer · 2011-12-29 09:18:10Z

7

If caching is acceptable it always should be used. Something like:

from django.core.cache import cache

cached = cache.get('temp_entries')
if cached:
    result = cached 
else:
    result = Temp_entry.objects.filter(sensor=49).order_by('dateTime').reverse().values_list()[:300]
    cache.set('temp_entries', result, 60*20)  # 20 min

Also you can set db_indexes for the appropriate columns

class Temp_entry(models.Model):
    dateTime = models.IntegerField(db_index=True) #datetime
    sensor = models.IntegerField(db_index=True)   # id of sensor
    temp = models.IntegerField()     # temp as temp in Kelvin * 100

edited Dec 29, 2011 at 9:18

answered Dec 28, 2011 at 7:18

Alexey Savanovich

1,90311 silver badges19 bronze badges

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

3 Comments

Andy Over a year ago

Thanks Alexey, two followup questions: is there a way to have the query run every 20 minutes instead of waiting for the request to check if it's stale? What does the db_indexes do?

Alexey Savanovich Over a year ago

Andy, db_index just creates normal database index, which will increase a query speed. However, this speedup is nothing compared to caching. Also note that such DBs like Mysql and Postgre has internal caching of queries, but not sure about sqlite. According to the first question: yes you can using django command and something like cron or celery but I think this is not good solution

Andy Over a year ago

Thanks Alexey, using the indexes and caching the view is rendering in an acceptable amount of time

StefanoP · Accepted Answer · 2011-12-29 09:24:04Z

3

Johnny Cache! http://packages.python.org/johnny-cache/ It works out-of-the-box, and it works well!

answered Dec 29, 2011 at 9:24

StefanoP

3,9182 gold badges22 silver badges26 bronze badges

1 Comment

Stefano Over a year ago

Good solution, though how good it is depend on your R/W patterns!

Daniel Roseman · Accepted Answer · 2011-12-28 11:01:07Z

2

You probably need to add some more indexes in your database. Use the django-debug toolbar to get the SQL of the actual query that's being run, and use the EXPLAIN feature to show what indexes it's using. For this particular query, I'd imagine you need to add an index on (sensor, dateTime) - do that directly in the database shell.

answered Dec 28, 2011 at 11:01

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

1 Comment

yurisich Over a year ago

in another comment on this thread the OP asked a question about what an index does. I thought I'd point that out for you.

jeffknupp · Accepted Answer · 2011-12-28 07:20:16Z

0

Well, if you know your entries always have an increasing dateTime (i.e. the dateTime is set when the entry is created and not edited), then you don't have to order by dateTime as they will naturally be in that order in the database.

answered Dec 28, 2011 at 7:20

jeffknupp

6,3643 gold badges30 silver badges29 bronze badges

Collectives™ on Stack Overflow

Improving performance of django DB query

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related