Improve SQL Queries

jeffshek · Sep 11, 2020 · f7e9689 · f7e9689
1 parent 14cad1c
commit f7e9689
Show file tree

Hide file tree

Showing 2 changed files with 30 additions and 8 deletions.
diff --git a/open/core/betterself/tests/views/test_overview_view.py b/open/core/betterself/tests/views/test_overview_view.py
@@ -21,8 +21,7 @@
 
 """
 python manage.py test --pattern="*test_overview_view.py" --keepdb
-
-dpy test open.core.betterself.tests.views.test_overview_view.OverviewTestView.test_view_response_for_productivity --keepdb
+dpy test open.core.betterself.tests.views.test_overview_view.OverviewTestView --keepdb
 """
 
 
@@ -35,7 +34,7 @@ def setUpTestData(cls):
         cls.end_period = get_utc_now()
         cls.end_period_date_string = cls.end_period.date().strftime(yyyy_mm_dd_format_1)
 
-        supplements = SupplementFactory.create_batch(2, user=user_1)
+        supplements = SupplementFactory.create_batch(10, user=user_1)
 
         for index in range(100):
             # simulate some missing data
@@ -91,6 +90,9 @@ def test_view_weekly(self):
         self.assertEqual(response.status_code, 200, response.data)
 
     def test_view_response(self):
+        """
+        dpy test open.core.betterself.tests.views.test_overview_view.OverviewTestView.test_view_response_for_productivity --keepdb
+        """
         start_period = "2020-08-22"
         kwargs = {"period": "monthly", "date": start_period}
         url = reverse(BetterSelfResourceConstants.OVERVIEW, kwargs=kwargs)
@@ -121,6 +123,7 @@ def test_view_response_for_supplements(self):
         # garbage assertion, but i'll write better tests when i can visualize how the frontend
         # should display the data
         self.assertIsNotNone(supplements_data)
+        self.assertIsNotNone(supplements_data["summary"])
 
     def test_view_response_for_productivity_no_data(self):
         start_period = get_time_relative_units_ago(self.end_period, days=7)
@@ -215,3 +218,16 @@ def test_view_response_for_sleep_data(self):
 
         expected_sleep_minutes = total_sleep_hours * 60
         self.assertAlmostEquals(total_duration_minutes, expected_sleep_minutes, 0)
+
+    def test_view_response_for_sql_queries(self):
+        """
+        dpy test open.core.betterself.tests.views.test_overview_view.OverviewTestView.test_view_response_for_sql_queries --keepdb
+        """
+        start_period = get_time_relative_units_ago(self.end_period, days=30)
+        start_period_string = start_period.date().strftime(yyyy_mm_dd_format_1)
+
+        kwargs = {"period": "weekly", "date": start_period_string}
+        url = reverse(BetterSelfResourceConstants.OVERVIEW, kwargs=kwargs)
+
+        with self.assertNumQueriesLessThan(15):
+            self.client_1.get(url)
diff --git a/open/core/betterself/utilities/history_overview_utilities.py b/open/core/betterself/utilities/history_overview_utilities.py
@@ -2,6 +2,7 @@
 from collections import defaultdict
 from datetime import datetime
 
+import dateutil.parser
 import pandas as pd
 from django.db.models import Sum
 
@@ -10,7 +11,6 @@
 from open.core.betterself.models.daily_productivity_log import DailyProductivityLog
 from open.core.betterself.models.food_logs import FoodLog
 from open.core.betterself.models.sleep_log import SleepLog
-from open.core.betterself.models.supplement import Supplement
 from open.core.betterself.models.supplement_log import SupplementLog
 from open.core.betterself.models.well_being_log import WellBeingLog
 from open.core.betterself.serializers.activity_log_serializers import (
@@ -35,8 +35,6 @@
     mean_time,
 )
 
-import dateutil.parser
-
 PRODUCTIVITY_LOG_VALUE_FIELDS = [
     "uuid",
     "date",
@@ -83,11 +81,20 @@ def get_overview_supplements_data(user, start_period, end_period):
     if not supplement_logs.exists():
         return response
 
+    supplement_name_cache = {}
+
     for log in supplement_logs:
         # otherwise everything is stored on the UTC date for the user, which doesn't make as much sense
         user_timezone = user.timezone
         normalized_time = user_timezone.normalize(log.time)
         log_date = normalized_time.date().isoformat()
+        supplement = log.supplement
+
+        # serialize this data once to avoid duplicate SQL queries
+        if supplement.uuid not in supplement_name_cache:
+            supplement_name_cache[supplement.name] = SimpleSupplementReadSerializer(
+                supplement
+            ).data
 
         serialized_log = SupplementLogReadSerializer(log).data
 
@@ -105,8 +112,7 @@ def get_overview_supplements_data(user, start_period, end_period):
     for value in taken_data:
         taken_result = {}
 
-        supplement = Supplement.objects.get(name=value["supplement__name"], user=user)
-        supplement_serialized = SimpleSupplementReadSerializer(supplement).data
+        supplement_serialized = supplement_name_cache[value["supplement__name"]]
         taken_result["supplement"] = supplement_serialized
 
         # add the individual total quantity to the aggregrate