Geek Answers Handbook

Improving django DB query performance

I am 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 am trying to get the last 300 Temp_entry elements to put them in a graph. I do like this:

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

However, this request takes ~ 1 second. Is there any way to improve this? I dug up and found that order_by is terribly inefficient, so I hope there is a viable alternative?

An alternative that I thought about, but I can’t figure out how to implement it, is to run the request every 20 minutes and save it in the cache, which will also be acceptable, as the data can be a little outdated without any side effects.

+5
source share
4 answers

, . - :

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

db_indexes

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
+6

, . django-debug, SQL EXPLAIN, , . , (sensor, dateTime) - .

+2

Well, if you know that your records always have an increasing dateTime value (i.e. dateTime is set when the record is created and not edited), you do not need to order by time date, since they will naturally be in that order in the database .

-1
source

More articles:


All Articles