Saturday, November 08, 2008
Django Tip: Cache List instead of QuerySet to minimize queries to the database
While playing with django debug toolbar, I noticed that some page on my application causes serious amount of queries that is being executed. One page actually executes around 580 queries so I investigated and noticed that this was caused probably by lazy initialization, not using select_related() on my queries and i'm caching QuerySet object instead of list.
An example of a getter function on my model:
@property
def data(self):
cache_data = getattr(self, 'cache_data', None)
if cache_data:
return cache_data
else:
self.cache_data = TimecardData.objects.filter(timecard_day=self)
return self.cache_data
The code above retrieves all the associated data on my timecard but to prevent it from hitting the database everytime i call that data() method, i need to cache it. One mistake I did is to cache the returned query set instead of casting it to a list type because whenever the cache_data is retrieved, It will again hit the database since it's only a query set.
After refactoring the above code to this one:
@propertyThe queries was reduced from 560 to 300 queries.
def data(self):
cache_data = getattr(self, 'cache_data', None)
if cache_data:
return cache_data
else:
self.cache_data = list(TimecardData.objects.select_related()
.filter(timecard_day=self))
return self.cache_data
Further looking at the problem, I refactored the code to this one:
I still have a long way debugging the problem but I hope this helps you aswell.
@propertyThe code above to this one checks using "if cache_data" but since empty list returns false if evaluated, it queries the database again instead of using cache. This one brought the queries from 580 to 120 (a very big difference)
def data(self):
cache_data = getattr(self, 'cache_data', None)
if cache_data != None:
return cache_data
else:
self.cache_data = list(TimecardData.objects.select_related().filter(timecard_day=self))
return self.cache_data
