I'm really stumped on an issue. On my website, whenever you pan the map, an AJAX call is fired, doing a query in the database. The problem is, the AJAX call takes somewhere between 2-10 seconds, which is unacceptable.
There are about 500k records in my database. I notice that the more records I add, the slower it gets. That makes sense right, but why is it exceptionally slow and so inconsistent?
I am using Digital Ocean. When I check my control panel, the CPU/RAM/disk are all operating at very low levels.
The AJAX call code:
def filter_closed_listings(request):
zoom = int(request.GET.get('zoomLevel'))
minLat = request.GET.get('minLat')
maxLat = request.GET.get('maxLat')
minLng = request.GET.get('minLng')
maxLng = request.GET.get('maxLng')
sold = request.GET.get('sold')
property_type = request.GET.get('pType')
building_style = request.GET.get('bType')
showActiveListings = request.GET.get('showActiveListings')
showSoldListings = request.GET.get('showSoldListings')
showEndedListings = request.GET.get('showEndedListings')
bed = request.GET.get('bed')
bath = request.GET.get('bath')
low_price = request.GET.get('low')
high_price = request.GET.get('high')
includePandL = request.GET.get('includePandL')
transaction_type = request.GET.get('transactionType')
ended_time_difference = datetime.date.today() + datetime.timedelta(-int(sold)) # Date check
print(ended_time_difference)
# Initial filter with map bounds and date check
data = Listing.objects.filter(la__gt=minLat).filter(la__lt=maxLat).filter(lo__gt=minLng).filter(lo__lt=maxLng)
data = data.filter(transaction_type=transaction_type)
# 0 is 'Any'. Therefore a filter is done if selection is not 'Any'.
if not property_type == '0':
if property_type == '1':
data = data.filter(Q(property_type='Condo Apt') | Q(property_type='Comm Element Condo'))
elif property_type == '2':
data = data.filter(property_type='Detached')
elif property_type == '3':
data = data.filter(property_type='Semi-Detached')
elif property_type == '4':
data = data.filter(Q(property_type='Att/Row/Twnhouse') | Q(property_type='Condo Townhouse'))
else:
data = data.exclude(Q(property_type='Condo Apt') | Q(property_type='Comm Element Condo') | Q(property_type='Detached') | Q(property_type='Semi-Detached') | Q(property_type='Att/Row/Twnhouse') | Q(property_type='Condo Townhouse'))
if showActiveListings == 'n':
data = data.filter(Q(status='Ter') | Q(status='Exp') | Q(status='Sld') | Q(status='Lsd'))
if showSoldListings == 'n':
if transaction_type == 'Sale':
data = data.exclude(status='Sld')
else:
data = data.exclude(status='Lsd')
else:
if transaction_type == 'Sale':
data = data.exclude(Q(status='Sld') & Q(sold_date__lt=ended_time_difference))
else:
data = data.exclude(Q(status='Lsd') & Q(sold_date__lt=ended_time_difference))
if showEndedListings == 'n':
data = data.exclude(Q(status='Ter') | Q(status='Exp'))
else:
data = data.exclude(Q(status='Exp') & Q(expiry_date__lt=ended_time_difference)).exclude(Q(status='Ter') & Q(terminated_date__lt=ended_time_difference))
if includePandL == 'false':
data = data.exclude(Q(property_type='Parking Space') | Q(property_type='Locker'))
# Bedrooms check
if not bed == 'undefined':
bed = bed.split(',')
queries = [Q(bedrooms_ag=b) for b in bed]
query = queries.pop()
if bed.pop() == '6':
query = Q(bedrooms_ag__gte=5)
for i in queries:
query |= i
#print(query)
data = data.filter(query)
# Bathrooms check
if not bath == 'undefined':
bath = bath.split(',')
queries = [Q(washrooms=b) for b in bath]
query = queries.pop()
if bath.pop() == '6':
query = Q(washrooms__gte=5)
for i in queries:
query |= i
#print(query)
data = data.filter(query)
# Filters low price and high price
if high_price == '0' and low_price == '0':
pass
elif high_price =='0':
data = data.filter(Q(Q(list_price__gte=low_price) & Q(list_price__lte=999999999) & Q(sold_price__isnull=True)) | Q(Q(sold_price__gte=low_price) & Q(sold_price__lte=999999999) & Q(sold_price__isnull=False))) # Nested Q object. Use list price if sold price is null.
else:
data = data.filter(Q(Q(list_price__gte=low_price) & Q(list_price__lte=high_price) & Q(sold_price__isnull=True)) | Q(Q(sold_price__gte=low_price) & Q(sold_price__lte=high_price) & Q(sold_price__isnull=False)))
if data.count() > 500:
return JsonResponse({'over500': True})
data = data.values('id', 'la', 'lo')
# Determines lat/lng precision based on zoom level
for i in data:
if zoom >= 13:
i['lo'] = round(i['lo'], 4)
i['la'] = round(i['la'], 4)
elif zoom > 9 and zoom < 13:
i['lo'] = round(i['lo'], 2)
i['la'] = round(i['la'], 2)
else:
i['lo'] = round(i['lo'], 1)
i['la'] = round(i['la'], 1)
return JsonResponse({'results': list(data)}) # This uses list() which is bad design. But only 3 fields are passed and highly unlikely to change so this can stay like this.
