CODEWITHSUNDEEP
pythondjangoperformancesqlite
Andy
Andy

Reputation: 46534

Improving performance of django DB query

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.

Upvotes: 6

Views: 4654

Answers (4)

StefanoP
StefanoP

Reputation: 3898

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

Upvotes: 3

Alexey Savanovich
Alexey Savanovich

Reputation: 1903

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

Upvotes: 7

Daniel Roseman
Daniel Roseman

Reputation: 600059

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.

Upvotes: 2

jeffknupp
jeffknupp

Reputation: 6314

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.

Upvotes: 0

Related Questions