Optimise SQL queries when displaying bills for a project

By defaut, SQLAlchemy uses lazy loading, which means that displaying n
bills will generate around n queries (to get the list of owers of each
bill).  Pre-load the list of owers to drastically decrease the number of
SQL queries.

Before this commit: 1004 SQL queries, 7535 ms elapsed time, 7536 ms CPU time, 530 ms SQL time

After this commit: 5 SQL queries, 3342 ms elapsed time, 3393 ms CPU time, 15 ms SQL time

Measured request: display the list of all bills for the project (without displaying the sidebar with balances)

Test setup to measure performance improvement:

- 5 users with various weights
- 1000 bills, each paid by a random user, each involving all 5 users
- laptop with Celeron N2830@2.16 GHz, SSD Samsung 850 EVO
- sqlite database on SSD, using sqlite 3.15.2
- python 2.7.13
- Flask-DebugToolbar 0.10.0 (to count SQL queries and loading time)

Performance measurements (using Flask-DebugToolbar with the second
request, to avoid measuring cold-cache performance):

- number of SQL queries
- elapsed time (from request to response)
- total CPU time consumed by the server handling the request
- total time spent on SQL queries (as reported by SQLAlchemy)

1 files changed, 3 insertions, 1 deletions

diff --git a/budget/web.py b/budget/web.py
index 63fbe4d..87aef26 100644
--- a/budget/web.py
+++ b/budget/web.py
@@ -15,6 +15,7 @@ from flask.ext.mail import Mail, Message
 from flask.ext.babel import get_locale, gettext as _
 from smtplib import SMTPRecipientsRefused
 import werkzeug
+from sqlalchemy import orm
 
 # local modules
 from models import db, Project, Person, Bill
@@ -277,7 +278,8 @@ def list_bills():
     # set the last selected payer as default choice if exists
     if 'last_selected_payer' in session:
         bill_form.payer.data = session['last_selected_payer']
-    bills = g.project.get_bills()
+    # Preload the "owers" relationship for all bills
+    bills = g.project.get_bills().options(orm.subqueryload(Bill.owers))
 
     return render_template("list_bills.html",
             bills=bills, member_form=MemberForm(g.project),