I'm working on a big page with different product statistics. I use there multiple charts, tables etc. It takes for example 5 seconds to load the page.
This is a very simplified models.py
EDIT
For example, OccurencesTable contains this row:
last_scan_time = tables.columns.TemplateColumn("""{{ record.get_last_scan.datetime }}""",accessor='get_last_scan.datetime', verbose_name='Last scan time')
So for each row, there must be executed query to the database which calls last scan. Better approach would be to preload all Scan objects.
PRODUCT
class Product(models.Model):
user = models.ForeignKey(User, null=False, blank=False, related_name='products')
name = models.CharField(max_length=200)
def get_occurences(self):
return self.occurences.prefetch_related("scans__price__currency")
def get_occurences_count(self):
return self.occurences.all().count()
def get_all_scans(self):
return [item for sublist in [x.scans.all() for x in self.get_occurences()] for item in sublist]
def get_all_valid_scans(self):
return [item for sublist in [x.get_valid_scans() for x in self.get_occurences()] for item in sublist]
def get_last_scans(self):
scans = []
for occ in self.get_occurences():
scan = occ.get_last_scan()
if scan:
scans.append(scan)
return scans
# @property
#def last_scan_time(self):
#scans = sorted([x for x in self.get_last_scans() if x.datetime],key = lambda x: x.datetime, reverse=True)
#Scan.objects.filter(occurence__product=self,price__amount__isnull=False)
#return str(scans[0].datetime)
def get_last_min_scan(self):
sorted_last_scans = [x for x in self.get_last_scans() if x.valid]
sorted_last_scans.sort(key=lambda x: x.price.eur_price)
return sorted_last_scans[0] if sorted_last_scans else None
def get_last_min_price(self):
last_scan = self.get_last_min_scan()
if last_scan:
return last_scan.price.eur_price
return None
def get_active_occurences(self):
return self.get_occurences().filter(active=True)
OCCURENCE
class Occurence(models.Model):
product = models.ForeignKey(Product, related_name='occurences', on_delete=models.CASCADE)
_last_scan = models.OneToOneField('Scan',null=True,blank=True,related_name='+')
currency = models.ForeignKey('Currency',related_name='occurences')
def get_last_scan(self):
try:
last = self.scans.select_related("price__amount").order_by('datetime').last()
except:
last = None
return last
def get_last_valid_scan(self):
try:
last = self.scans.exclude(price__isnull=True).order_by('-datetime').first()
except:
last = None
return last
def get_second_last_valid_scan(self):
scans = self.scans.exclude(price__isnull=True).order_by('-datetime').select_related("price")
if scans.count()>=2:
return scans[1]
return None
def get_valid_scans(self):
return self.scans.all().exclude(price__isnull=True)
def get_min_scan(self):
scan = self.get_valid_scans().order_by('price__amount').first()
if scan:
return scan
return None
""" STATS METHODS """
def stats_get_difference_for_two_last_scans(self):
second_last_valid_scan = self.get_second_last_valid_scan()
if second_last_valid_scan:
difference_in_percent = math_ops.round_eur(decimal.Decimal(-1 * (100 - self.get_last_valid_scan().price.eur_price / second_last_valid_scan.price.eur_price * 100), 2))
else:
difference_in_percent = decimal.Decimal(0)
return {'percent':difference_in_percent,
'xml_tag':'<two_last_scans_difference_in_percent>',
'xml_close_tag':'</two_last_scans_difference_in_percent>',
'label':'Last scans diff'}
def stats_get_min_price(self):
scan = self.get_min_scan()
if scan:
price = scan.price.eur_price
else:
price = None
return {'price': price,
'xml_tag': '<min_price>',
'xml_close_tag': '</min_price>',
'label': 'Min'}
def stats_get_avg_price(self):
prices = [x.price for x in self.scans.all() if x.price]
if prices:
price = math_ops.round_eur(decimal.Decimal(sum([x.eur_price for x in prices]) / len(prices), 2))
else:
price = None
preferred_currency = self.product.user.userprofile.preferred_currency
if preferred_currency:
if preferred_currency.shortcut == 'czk':
amount = Exchange.eur_to_czk(price)
pref_currency_string = '{} CZK'.format(amount)
pref_currency_amount = amount
else:
amount = price
pref_currency_string = u'{} €'.format(amount)
pref_currency_amount = amount
else:
if self.currency.shortcut == 'czk':
amount = Exchange.eur_to_czk(price)
pref_currency_string = '{} CZK'.format(amount)
pref_currency_amount = amount
else:
amount = price
pref_currency_string = u'{} €'.format(amount)
pref_currency_amount = amount
return {'price': price,
'pref_currency_string':pref_currency_string,
'pref_currency_amount':pref_currency_amount,
'xml_tag': '<average_price>',
'xml_close_tag': '</average_price>',
'label': 'AVG'}
PRICE
class Price(models.Model):
currency = models.ForeignKey('Currency',related_name='prices')
amount = models.DecimalField(max_digits=10,decimal_places=2)
def __unicode__(self):
return u'{} {}'.format(self.amount,self.currency)
def to_eur(self):
if self.currency.shortcut=='eur':
return self.amount
elif self.currency.shortcut=='czk':
return Exchange.objects.first().czk_to_eur(self.amount)
def to_czk(self):
if self.currency.shortcut == 'czk':
return self.amount
elif self.currency.shortcut == 'eur':
return Exchange.objects.first().eur_to_czk(self.amount)
@property
def eur_price(self):
if self.currency.shortcut=='eur':
return self.amount
elif self.currency.shortcut=='czk':
return self.to_eur()
@property
def czk_price(self):
cents = decimal.Decimal('01')
if self.currency.shortcut == 'czk':
return (self.amount).quantize(cents, decimal.ROUND_HALF_UP)
elif self.currency.shortcut == 'eur':
return self.to_czk()
@property
def pref_currency_amount(self):
pref_currency = self.scan.occurence.product.user.userprofile.preferred_currency
if pref_currency:
if pref_currency.shortcut == 'czk':
return self.czk_price
else: return self.eur_price
return self.amount
@property
def pref_currency_string(self):
pref_currency = self.scan.occurence.product.user.userprofile.preferred_currency
# return pref_currency.shortcut
if pref_currency:
if pref_currency.shortcut.lower() == 'czk':
return u'{} {}'.format(self.czk_price, pref_currency.shortcut)
else:
return u'{} {}'.format(self.eur_price, pref_currency.special_sign)
return u'{} {}'.format(self.amount,self.currency.special_sign)
def get_price(self,currency):
if currency=='eur':
return self.eur_price
elif currency=='czk':
return self.czk_price
def get_exchanged_price_string(self):
if self.currency.shortcut=='czk':
return u'{} {}'.format(Exchange.czk_to_eur(self.amount),u'€')
else:
return u'{} {}'.format(Exchange.eur_to_czk(self.amount),'CZK')
def get_original_price_string(self):
if self.currency.shortcut=='czk':
return u'{} {}'.format(self.amount,u'€')
else:
return u'{} {}'.format(Exchange.eur_to_czk(self.amount),'CZK')
For example rendering table occurences takes almost 2 seconds according to django-debug-toolbar. I'm trying to optimize it using select_related and prefetch_related but its still slow.
It's because of different methods I call has the same queries and those queries are called multiple times.
class OccurencesTable(tables.Table):
site = tables.columns.TemplateColumn("""<a href="{{ record.url }}">{{ record.site.name }}</a>""",accessor='site.name', verbose_name=u'Site')
avg_price = tables.columns.TemplateColumn("""{{ record.stats_get_avg_price.pref_currency_string }}""",accessor='stats_get_avg_price.price', verbose_name='AVG price')
last_scan_price = tables.columns.TemplateColumn("""{{ record.get_last_scan.price.pref_currency_string }} """,accessor='get_last_scan.price.amount', verbose_name='Last scan price')
last_scan_time = tables.columns.TemplateColumn("""{{ record.get_last_scan.datetime }}""",accessor='get_last_scan.datetime', verbose_name='Last scan time')
difference = tables.columns.TemplateColumn("""{% load static %}{% with diff=record.stats_get_difference_for_two_last_scans.percent %}
{% if diff > 0 %}+{% endif %}{{ diff }} % <img style="height: 15px" src="{% if diff < 0 %}{% static "img/icons/arrow-trend-minus.png" %}{% elif diff == 0 %}{% static "img/icons/arrow-trend-normal.png" %}{% else %}{% static "img/icons/arrow-trend-plus.png" %}{% endif %}">
{% endwith %}""",verbose_name='Difference')
class Meta:
model = Occurence
fields = ('id', 'site', 'last_scan_time','last_scan_price', 'difference', 'avg_price')
attrs = {'id': 'id_occurences_table',
'class': 'table', }
Can't figure out how to optimize methods of models Occurence and Product. Do you have any ideas?
With code like this, you should be thrilled with the timing that you get
This code is retrieving an entire table by the call to get_last_scans() then you are sorting that result inside python code! Databases have very fast built in sort functionality. Please use it.
There are plenty of other functions like this in this code. You will have to fix each of them. Do the filtering and sorting in the database. Not in your python code.