prompt: We are currently solving the following issue within our repository. Here is the issue text:
  --- BEGIN ISSUE ---
  Improve performance of _get_creator_counts util function
  `kitsune.community.utils._get_creator_counts` until function is DB heavy and takes a lot of time to execute. Evaluate its usefulness and provide a way to optimize the query and/or cache the results.  
  
  This issue is related to the degraded performance SUMO experienced on Fri March 30th ([NR Error](https://rpm.newrelic.com/accounts/1299394/applications/45097089/downtime/34422892))
  
  --- END ISSUE ---
  
  Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
  --- BEGIN FILES ---
  Path: `kitsune/community/utils.py`
  Content:
  ```
  1 import hashlib
  2 
  3 from datetime import datetime, date, timedelta
  4 from django.conf import settings
  5 from django.core.cache import cache
  6 from django.db.models import Count, F
  7 
  8 from kitsune.products.models import Product
  9 from kitsune.questions.models import Answer
  10 from kitsune.users.models import User
  11 from kitsune.users.templatetags.jinja_helpers import profile_avatar
  12 from kitsune.wiki.models import Revision
  13 
  14 
  15 def top_contributors_questions(start=None, end=None, locale=None, product=None,
  16                                count=10, page=1, use_cache=True):
  17     """Get the top Support Forum contributors."""
  18     if use_cache:
  19         cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)
  20         cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()
  21         cache_key = 'top_contributors_questions_{}'.format(cache_key)
  22         cached = cache.get(cache_key, None)
  23         if cached:
  24             return cached
  25 
  26     answers = (Answer.objects
  27                .exclude(is_spam=True)
  28                .exclude(question__is_spam=True)
  29                # Adding answer to your own question, isn't a contribution.
  30                .exclude(creator_id=F('question__creator_id')))
  31 
  32     if start is None:
  33         # By default we go back 90 days.
  34         start = date.today() - timedelta(days=90)
  35         answers = answers.filter(created__gte=start)
  36     if end:
  37         # If no end is specified, we don't need to filter by it.
  38         answers = answers.filter(created__lt=end)
  39     if locale:
  40         answers = answers.filter(question__locale=locale)
  41     if product:
  42         if isinstance(product, Product):
  43             product = product.slug
  44         answers = answers.filter(question__product__slug=product)
  45 
  46     users = (User.objects
  47              .filter(answers__in=answers)
  48              .annotate(query_count=Count('answers'))
  49              .order_by('-query_count'))
  50     counts = _get_creator_counts(users, count, page)
  51 
  52     if use_cache:
  53         cache.set(cache_key, counts, 60*15)  # 15 minutes
  54     return counts
  55 
  56 
  57 def top_contributors_kb(start=None, end=None, product=None, count=10, page=1, use_cache=True):
  58     """Get the top KB editors (locale='en-US')."""
  59     return top_contributors_l10n(
  60         start, end, settings.WIKI_DEFAULT_LANGUAGE, product, count, use_cache)
  61 
  62 
  63 def top_contributors_l10n(start=None, end=None, locale=None, product=None,
  64                           count=10, page=1, use_cache=True):
  65     """Get the top l10n contributors for the KB."""
  66     if use_cache:
  67         cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)
  68         cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()
  69         cache_key = u'top_contributors_l10n_{}'.format(cache_key)
  70         cached = cache.get(cache_key, None)
  71         if cached:
  72             return cached
  73 
  74     # Get the user ids and contribution count of the top contributors.
  75     revisions = Revision.objects.all()
  76     if locale is None:
  77         # If there is no locale specified, exclude en-US only. The rest are
  78         # l10n.
  79         revisions = revisions.exclude(document__locale=settings.WIKI_DEFAULT_LANGUAGE)
  80     if start is None:
  81         # By default we go back 90 days.
  82         start = date.today() - timedelta(days=90)
  83         revisions = revisions.filter(created__gte=start)
  84     if end:
  85         # If no end is specified, we don't need to filter by it.
  86         revisions = revisions.filter(created__lt=end)
  87     if locale:
  88         revisions = revisions.filter(document__locale=locale)
  89     if product:
  90         if isinstance(product, Product):
  91             product = product.slug
  92         revisions = revisions.filter(document__products__slug=product)
  93 
  94     users = (User.objects
  95              .filter(created_revisions__in=revisions)
  96              .annotate(query_count=Count('created_revisions'))
  97              .order_by('-query_count'))
  98     counts = _get_creator_counts(users, count, page)
  99 
  100     if use_cache:
  101         cache.set(cache_key, counts, 60*15)  # 15 minutes
  102     return counts
  103 
  104 
  105 def top_contributors_aoa(start=None, end=None, locale=None, count=10, page=1, use_cache=True):
  106     """Get the top Army of Awesome contributors."""
  107     # AoA is deprecated, return 0 until we remove all related code.
  108     return ([], 0)
  109 
  110 
  111 def _get_creator_counts(query, count, page):
  112     total = query.count()
  113     results = []
  114     now = datetime.now()
  115     for user in query[((page - 1) * count):(page * count)]:
  116         last_contribution_date = user.profile.last_contribution_date
  117         days_since_last_activity = None
  118         if last_contribution_date:
  119             days_since_last_activity = now - last_contribution_date
  120 
  121         data = {
  122             'count': user.query_count,
  123             'term': user.id,
  124             'user': {
  125                 'id': user.id,
  126                 'username': user.username,
  127                 'display_name': user.profile.display_name,
  128                 'avatar': profile_avatar(user, size=120),
  129                 'twitter_usernames': user.profile.twitter_usernames,
  130                 'last_contribution_date': last_contribution_date,
  131                 'days_since_last_activity': days_since_last_activity,
  132             }
  133         }
  134         results.append(data)
  135 
  136     return (results, total)
  137
  ```
  --- END FILES ---
  
  Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.
  
  Here is an example:
  
  ```diff
  diff --git a/examples/server_async.py b/examples/server_async.py
  --- a/examples/server_async.py
  +++ b/examples/server_async.py
  @@ -313,4 +313,4 @@
   
   
   if __name__ == "__main__":
  -    asyncio.run(run_async_server("."), debug=True)
  +    asyncio.run(run_async_server(), debug=True)
  diff --git a/examples/server_sync.py b/examples/server_sync.py
  --- a/examples/server_sync.py
  +++ b/examples/server_sync.py
  @@ -313,5 +313,5 @@
   
   
   if __name__ == "__main__":
  -    server = run_sync_server(".")
  +    server = run_sync_server()
       server.shutdown()
  
  ```
  
golden_diff: diff --git a/kitsune/community/utils.py b/kitsune/community/utils.py
  --- a/kitsune/community/utils.py
  +++ b/kitsune/community/utils.py
  @@ -7,8 +7,7 @@
   
   from kitsune.products.models import Product
   from kitsune.questions.models import Answer
  -from kitsune.users.models import User
  -from kitsune.users.templatetags.jinja_helpers import profile_avatar
  +from kitsune.users.models import User, UserMappingType
   from kitsune.wiki.models import Revision
   
   
  @@ -110,27 +109,36 @@
   
   def _get_creator_counts(query, count, page):
       total = query.count()
  +
  +    start = (page - 1) * count
  +    end = page * count
  +    query_data = query.values('id', 'query_count')[start:end]
  +
  +    query_data = {obj['id']: obj['query_count'] for obj in query_data}
  +
  +    users_data = (UserMappingType.search().filter(id__in=query_data.keys())
  +                                 .values_dict('id', 'username', 'display_name',
  +                                              'avatar', 'twitter_usernames',
  +                                              'last_contribution_date')[:count])
  +
  +    users_data = UserMappingType.reshape(users_data)
  +
       results = []
       now = datetime.now()
  -    for user in query[((page - 1) * count):(page * count)]:
  -        last_contribution_date = user.profile.last_contribution_date
  -        days_since_last_activity = None
  -        if last_contribution_date:
  -            days_since_last_activity = now - last_contribution_date
  +
  +    for u_data in users_data:
  +        user_id = u_data.get('id')
  +        last_contribution_date = u_data.get('last_contribution_date', None)
  +
  +        u_data['days_since_last_activity'] = ((now - last_contribution_date).days
  +                                              if last_contribution_date else None)
   
           data = {
  -            'count': user.query_count,
  -            'term': user.id,
  -            'user': {
  -                'id': user.id,
  -                'username': user.username,
  -                'display_name': user.profile.display_name,
  -                'avatar': profile_avatar(user, size=120),
  -                'twitter_usernames': user.profile.twitter_usernames,
  -                'last_contribution_date': last_contribution_date,
  -                'days_since_last_activity': days_since_last_activity,
  -            }
  +            'count': query_data.get(user_id),
  +            'term': user_id,
  +            'user': u_data
           }
  +
           results.append(data)
   
  -    return (results, total)
  +    return results, total
  
verification_info: {"golden_diff": "diff --git a/kitsune/community/utils.py b/kitsune/community/utils.py\n--- a/kitsune/community/utils.py\n+++ b/kitsune/community/utils.py\n@@ -7,8 +7,7 @@\n \n from kitsune.products.models import Product\n from kitsune.questions.models import Answer\n-from kitsune.users.models import User\n-from kitsune.users.templatetags.jinja_helpers import profile_avatar\n+from kitsune.users.models import User, UserMappingType\n from kitsune.wiki.models import Revision\n \n \n@@ -110,27 +109,36 @@\n \n def _get_creator_counts(query, count, page):\n     total = query.count()\n+\n+    start = (page - 1) * count\n+    end = page * count\n+    query_data = query.values('id', 'query_count')[start:end]\n+\n+    query_data = {obj['id']: obj['query_count'] for obj in query_data}\n+\n+    users_data = (UserMappingType.search().filter(id__in=query_data.keys())\n+                                 .values_dict('id', 'username', 'display_name',\n+                                              'avatar', 'twitter_usernames',\n+                                              'last_contribution_date')[:count])\n+\n+    users_data = UserMappingType.reshape(users_data)\n+\n     results = []\n     now = datetime.now()\n-    for user in query[((page - 1) * count):(page * count)]:\n-        last_contribution_date = user.profile.last_contribution_date\n-        days_since_last_activity = None\n-        if last_contribution_date:\n-            days_since_last_activity = now - last_contribution_date\n+\n+    for u_data in users_data:\n+        user_id = u_data.get('id')\n+        last_contribution_date = u_data.get('last_contribution_date', None)\n+\n+        u_data['days_since_last_activity'] = ((now - last_contribution_date).days\n+                                              if last_contribution_date else None)\n \n         data = {\n-            'count': user.query_count,\n-            'term': user.id,\n-            'user': {\n-                'id': user.id,\n-                'username': user.username,\n-                'display_name': user.profile.display_name,\n-                'avatar': profile_avatar(user, size=120),\n-                'twitter_usernames': user.profile.twitter_usernames,\n-                'last_contribution_date': last_contribution_date,\n-                'days_since_last_activity': days_since_last_activity,\n-            }\n+            'count': query_data.get(user_id),\n+            'term': user_id,\n+            'user': u_data\n         }\n+\n         results.append(data)\n \n-    return (results, total)\n+    return results, total\n", "issue": "Improve performance of _get_creator_counts util function\n`kitsune.community.utils._get_creator_counts` until function is DB heavy and takes a lot of time to execute. Evaluate its usefulness and provide a way to optimize the query and/or cache the results.  \r\n\r\nThis issue is related to the degraded performance SUMO experienced on Fri March 30th ([NR Error](https://rpm.newrelic.com/accounts/1299394/applications/45097089/downtime/34422892))\n", "before_files": [{"content": "import hashlib\n\nfrom datetime import datetime, date, timedelta\nfrom django.conf import settings\nfrom django.core.cache import cache\nfrom django.db.models import Count, F\n\nfrom kitsune.products.models import Product\nfrom kitsune.questions.models import Answer\nfrom kitsune.users.models import User\nfrom kitsune.users.templatetags.jinja_helpers import profile_avatar\nfrom kitsune.wiki.models import Revision\n\n\ndef top_contributors_questions(start=None, end=None, locale=None, product=None,\n                               count=10, page=1, use_cache=True):\n    \"\"\"Get the top Support Forum contributors.\"\"\"\n    if use_cache:\n        cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)\n        cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()\n        cache_key = 'top_contributors_questions_{}'.format(cache_key)\n        cached = cache.get(cache_key, None)\n        if cached:\n            return cached\n\n    answers = (Answer.objects\n               .exclude(is_spam=True)\n               .exclude(question__is_spam=True)\n               # Adding answer to your own question, isn't a contribution.\n               .exclude(creator_id=F('question__creator_id')))\n\n    if start is None:\n        # By default we go back 90 days.\n        start = date.today() - timedelta(days=90)\n        answers = answers.filter(created__gte=start)\n    if end:\n        # If no end is specified, we don't need to filter by it.\n        answers = answers.filter(created__lt=end)\n    if locale:\n        answers = answers.filter(question__locale=locale)\n    if product:\n        if isinstance(product, Product):\n            product = product.slug\n        answers = answers.filter(question__product__slug=product)\n\n    users = (User.objects\n             .filter(answers__in=answers)\n             .annotate(query_count=Count('answers'))\n             .order_by('-query_count'))\n    counts = _get_creator_counts(users, count, page)\n\n    if use_cache:\n        cache.set(cache_key, counts, 60*15)  # 15 minutes\n    return counts\n\n\ndef top_contributors_kb(start=None, end=None, product=None, count=10, page=1, use_cache=True):\n    \"\"\"Get the top KB editors (locale='en-US').\"\"\"\n    return top_contributors_l10n(\n        start, end, settings.WIKI_DEFAULT_LANGUAGE, product, count, use_cache)\n\n\ndef top_contributors_l10n(start=None, end=None, locale=None, product=None,\n                          count=10, page=1, use_cache=True):\n    \"\"\"Get the top l10n contributors for the KB.\"\"\"\n    if use_cache:\n        cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)\n        cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()\n        cache_key = u'top_contributors_l10n_{}'.format(cache_key)\n        cached = cache.get(cache_key, None)\n        if cached:\n            return cached\n\n    # Get the user ids and contribution count of the top contributors.\n    revisions = Revision.objects.all()\n    if locale is None:\n        # If there is no locale specified, exclude en-US only. The rest are\n        # l10n.\n        revisions = revisions.exclude(document__locale=settings.WIKI_DEFAULT_LANGUAGE)\n    if start is None:\n        # By default we go back 90 days.\n        start = date.today() - timedelta(days=90)\n        revisions = revisions.filter(created__gte=start)\n    if end:\n        # If no end is specified, we don't need to filter by it.\n        revisions = revisions.filter(created__lt=end)\n    if locale:\n        revisions = revisions.filter(document__locale=locale)\n    if product:\n        if isinstance(product, Product):\n            product = product.slug\n        revisions = revisions.filter(document__products__slug=product)\n\n    users = (User.objects\n             .filter(created_revisions__in=revisions)\n             .annotate(query_count=Count('created_revisions'))\n             .order_by('-query_count'))\n    counts = _get_creator_counts(users, count, page)\n\n    if use_cache:\n        cache.set(cache_key, counts, 60*15)  # 15 minutes\n    return counts\n\n\ndef top_contributors_aoa(start=None, end=None, locale=None, count=10, page=1, use_cache=True):\n    \"\"\"Get the top Army of Awesome contributors.\"\"\"\n    # AoA is deprecated, return 0 until we remove all related code.\n    return ([], 0)\n\n\ndef _get_creator_counts(query, count, page):\n    total = query.count()\n    results = []\n    now = datetime.now()\n    for user in query[((page - 1) * count):(page * count)]:\n        last_contribution_date = user.profile.last_contribution_date\n        days_since_last_activity = None\n        if last_contribution_date:\n            days_since_last_activity = now - last_contribution_date\n\n        data = {\n            'count': user.query_count,\n            'term': user.id,\n            'user': {\n                'id': user.id,\n                'username': user.username,\n                'display_name': user.profile.display_name,\n                'avatar': profile_avatar(user, size=120),\n                'twitter_usernames': user.profile.twitter_usernames,\n                'last_contribution_date': last_contribution_date,\n                'days_since_last_activity': days_since_last_activity,\n            }\n        }\n        results.append(data)\n\n    return (results, total)\n", "path": "kitsune/community/utils.py"}], "after_files": [{"content": "import hashlib\n\nfrom datetime import datetime, date, timedelta\nfrom django.conf import settings\nfrom django.core.cache import cache\nfrom django.db.models import Count, F\n\nfrom kitsune.products.models import Product\nfrom kitsune.questions.models import Answer\nfrom kitsune.users.models import User, UserMappingType\nfrom kitsune.wiki.models import Revision\n\n\ndef top_contributors_questions(start=None, end=None, locale=None, product=None,\n                               count=10, page=1, use_cache=True):\n    \"\"\"Get the top Support Forum contributors.\"\"\"\n    if use_cache:\n        cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)\n        cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()\n        cache_key = 'top_contributors_questions_{}'.format(cache_key)\n        cached = cache.get(cache_key, None)\n        if cached:\n            return cached\n\n    answers = (Answer.objects\n               .exclude(is_spam=True)\n               .exclude(question__is_spam=True)\n               # Adding answer to your own question, isn't a contribution.\n               .exclude(creator_id=F('question__creator_id')))\n\n    if start is None:\n        # By default we go back 90 days.\n        start = date.today() - timedelta(days=90)\n        answers = answers.filter(created__gte=start)\n    if end:\n        # If no end is specified, we don't need to filter by it.\n        answers = answers.filter(created__lt=end)\n    if locale:\n        answers = answers.filter(question__locale=locale)\n    if product:\n        if isinstance(product, Product):\n            product = product.slug\n        answers = answers.filter(question__product__slug=product)\n\n    users = (User.objects\n             .filter(answers__in=answers)\n             .annotate(query_count=Count('answers'))\n             .order_by('-query_count'))\n    counts = _get_creator_counts(users, count, page)\n\n    if use_cache:\n        cache.set(cache_key, counts, 60*15)  # 15 minutes\n    return counts\n\n\ndef top_contributors_kb(start=None, end=None, product=None, count=10, page=1, use_cache=True):\n    \"\"\"Get the top KB editors (locale='en-US').\"\"\"\n    return top_contributors_l10n(\n        start, end, settings.WIKI_DEFAULT_LANGUAGE, product, count, use_cache)\n\n\ndef top_contributors_l10n(start=None, end=None, locale=None, product=None,\n                          count=10, page=1, use_cache=True):\n    \"\"\"Get the top l10n contributors for the KB.\"\"\"\n    if use_cache:\n        cache_key = u'{}_{}_{}_{}_{}_{}'.format(start, end, locale, product, count, page)\n        cache_key = hashlib.sha1(cache_key.encode('utf-8')).hexdigest()\n        cache_key = u'top_contributors_l10n_{}'.format(cache_key)\n        cached = cache.get(cache_key, None)\n        if cached:\n            return cached\n\n    # Get the user ids and contribution count of the top contributors.\n    revisions = Revision.objects.all()\n    if locale is None:\n        # If there is no locale specified, exclude en-US only. The rest are\n        # l10n.\n        revisions = revisions.exclude(document__locale=settings.WIKI_DEFAULT_LANGUAGE)\n    if start is None:\n        # By default we go back 90 days.\n        start = date.today() - timedelta(days=90)\n        revisions = revisions.filter(created__gte=start)\n    if end:\n        # If no end is specified, we don't need to filter by it.\n        revisions = revisions.filter(created__lt=end)\n    if locale:\n        revisions = revisions.filter(document__locale=locale)\n    if product:\n        if isinstance(product, Product):\n            product = product.slug\n        revisions = revisions.filter(document__products__slug=product)\n\n    users = (User.objects\n             .filter(created_revisions__in=revisions)\n             .annotate(query_count=Count('created_revisions'))\n             .order_by('-query_count'))\n    counts = _get_creator_counts(users, count, page)\n\n    if use_cache:\n        cache.set(cache_key, counts, 60*15)  # 15 minutes\n    return counts\n\n\ndef top_contributors_aoa(start=None, end=None, locale=None, count=10, page=1, use_cache=True):\n    \"\"\"Get the top Army of Awesome contributors.\"\"\"\n    # AoA is deprecated, return 0 until we remove all related code.\n    return ([], 0)\n\n\ndef _get_creator_counts(query, count, page):\n    total = query.count()\n\n    start = (page - 1) * count\n    end = page * count\n    query_data = query.values('id', 'query_count')[start:end]\n\n    query_data = {obj['id']: obj['query_count'] for obj in query_data}\n\n    users_data = (UserMappingType.search().filter(id__in=query_data.keys())\n                                 .values_dict('id', 'username', 'display_name',\n                                              'avatar', 'twitter_usernames',\n                                              'last_contribution_date')[:count])\n\n    users_data = UserMappingType.reshape(users_data)\n\n    results = []\n    now = datetime.now()\n\n    for u_data in users_data:\n        user_id = u_data.get('id')\n        last_contribution_date = u_data.get('last_contribution_date', None)\n\n        u_data['days_since_last_activity'] = ((now - last_contribution_date).days\n                                              if last_contribution_date else None)\n\n        data = {\n            'count': query_data.get(user_id),\n            'term': user_id,\n            'user': u_data\n        }\n\n        results.append(data)\n\n    return results, total\n", "path": "kitsune/community/utils.py"}]}