We should do a census of our views to ensure querysets are optimized

[jacobdgm]

@ahankinson recently opened #1247 and #1248 (thank you!), adding .select_related to some key querysets and thus significantly reducing the number of database queries required to serve these pages.

About a year ago, we identified a bunch of views that were loading slowly and optimized them using .select_related, .prefetch_related, .values, and so on. It would be worth systematically checking all our views to ensure these optimizations are being consistently applied.

[ahankinson]

I don’t think it’s that complicated. I was able to do those fixes in a few minutes. My patented technique consists of:

• have the Django debug toolbar open with the sql profiler running.
• click around the site
• When you see a page that takes several hundred queries to load, fix it.
• Move on

The thing that will require more in-depth rewrites, though, are the queries within for loops. I see a common pattern of:

• get a list of ids as a value list
• Loop through that list and trigger a query for each loop
• The result is several hundred queries, in some cases

Those queries can’t be optimized easily, but they could probably be refactored to get all the records in one go. If necessary the records could be fetched and then a non-query loop can sort them into nested lists or whatever is necessary.

The other thing I see is naive use of query set methods within templates. It’s not uncommon to come across things like source.chants.all, which will trigger a database query if not properly handled in the view first.