CODEWITHSUNDEEP
pythondjango
Jglstewart
Jglstewart

Reputation: 706

Django Queryset Iteration Optimization

I've noticed that my view is taking a long time to load. I believe this is because of the number of queries I'm performing. My view looks like this:

facility_list = Facility.objects.all()
qset = facility_list.values('id','name','type',)
for row in qset:
    facility = facility_list.get(id = row['id']) 
    row['name'] = "<a href=\"%s/detail\">%s</a>" % (row['id'], row['name'])
    row['current_address'] = '%s' % facility.current_address
    row['region'] = '%s' % facility.current_address.region
    row['recent_rate'] = "%s" % facility.check_latest_rate()
    row['correct_rate'] = facility.get_rate_flag()
    row['energy_sum'] = facility.get_alltime_energy_sum()
    row['energy_avg_year'] = facility.get_avg_yearly_energy()
    row['demand_avg_year'] = facility.get_avg_yearly_demand()
    row['demand_peak_year'] =  facility.get_avg_yearly_peak_demand()
    row['percent_est_bills'] =  facility.get_percent_est_bills()
    row['total_cost'] =  facility.get_alltime_cost()
    row['avg_yearly_cost'] =  facility.get_avg_yearly_cost()
    row['per_cost_est'] =  facility.get_percent_est_cost()
    row['energy_sum_by_meter'] = facility.total_energy_by_meter()

As you can see, I'm iterating over a ValuesQuerySet and calling model functions on the specific facility that relates to each row in the ValuesQuerySet. I've read about cacheing and how if I was iterating over a Queryset it would only call to the database once. Also, I'm wondering if there is a way I can use select_related for current_address and current_address.region since they are both foreignkeys?

After I optimize this loop, I'll have to dive further into each model function as there are many queries in those as well.

Edit: When I check the run time for this entire iteration I get 19-23 seconds or maybe it's miliseconds. Yikes! That's huge. The database isn't that big.

Advice would be greatly appreciated.

Thanks!

Upvotes: 0

Views: 250

Answers (1)

Daniel Roseman
Daniel Roseman

Reputation: 600059

I don't understand the point of the ValuesQuerySet. You loop through it, but you then use the ID to do another db lookup in each row, to query the original item! Why do you do that? Why get the values at all? Why not just:

facility_list = Facility.objects.all().select_related('current_address__region')
rows = []
for facility in facility_list:
    row = {'id': facility.id, 'name': facility.name, 'type': facility.type}
    row['name'] = "<a href=\"%s/detail\">%s</a>" % (row['id'], row['name'])
    ...etc...
    rows.append(row)

Now (ignoring the subsequent lookups inside the methods) that is one single query, rather than one query per row.

Upvotes: 1

Related Questions