From 160a08bba5324b25abd9e866b884c91d75e597b0 Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Tue, 20 Nov 2012 15:57:05 -0600 Subject: Improve performance of todolists query Use some standard SQL and split the query into two different parts to save a lot of unnecessary sorting and field retrieval at the database level. The `CASE WHEN complete THEN 1 ELSE 0 END` syntax should be accepted by any database that implements proper SQL; it was tested in PostgreSQL and sqlite3 without issues. Signed-off-by: Dan McGee --- todolists/utils.py | 41 ++++++++++++++++++++++++++--------------- 1 file changed, 26 insertions(+), 15 deletions(-) diff --git a/todolists/utils.py b/todolists/utils.py index 94f39f71..03c47931 100644 --- a/todolists/utils.py +++ b/todolists/utils.py @@ -1,26 +1,37 @@ +from django.db import connections, router from django.db.models import Count -from main.models import Todolist +from main.models import Todolist, TodolistPkg -def get_annotated_todolists(incomplete_only=False): - qs = Todolist.objects.all() - lists = qs.select_related('creator').defer( - 'creator__email', 'creator__password', 'creator__is_staff', - 'creator__is_active', 'creator__is_superuser', - 'creator__last_login', 'creator__date_joined').annotate( - pkg_count=Count('todolistpkg')).order_by('-date_added') - incomplete = qs.filter(todolistpkg__complete=False).annotate( - Count('todolistpkg')).values_list('id', 'todolistpkg__count') +def todo_counts(): + sql = """ +SELECT list_id, count(*), sum(CASE WHEN complete THEN 1 ELSE 0 END) + FROM todolist_pkgs + GROUP BY list_id + """ + database = router.db_for_write(TodolistPkg) + connection = connections[database] + cursor = connection.cursor() + cursor.execute(sql) + results = cursor.fetchall() + return {row[0]: (row[1], row[2]) for row in results} - lookup = dict(incomplete) - if incomplete_only: - lists = lists.filter(id__in=lookup.keys()) +def get_annotated_todolists(incomplete_only=False): + lists = Todolist.objects.all().select_related( + 'creator').order_by('-date_added') + lookup = todo_counts() - # tag each list with an incomplete package count + # tag each list with package counts for todolist in lists: - todolist.incomplete_count = lookup.get(todolist.id, 0) + counts = lookup.get(todolist.id, (0, 0)) + todolist.pkg_count = counts[0] + todolist.complete_count = counts[1] + todolist.incomplete_count = counts[0] - counts[1] + + if incomplete_only: + lists = [l for l in lists if l.incomplete_count > 0] return lists -- cgit v1.2.3-55-g3dc8