Repository: oppia/oppia
Issue category: Performance Issue

Slow load times for translation Opportunities
<!--
  - Thanks for taking the time to report a bug in the Oppia project.
  - Before filing a new issue, please do a quick search to check that it hasn't
  - already been filed on the [issue tracker](https://github.com/oppia/oppia/issues)._
  -->

**Describe the bug**
opportunitiessummaryhandler/translation takes 6-7 seconds to execute on production

**To Reproduce**
Steps to reproduce the behavior:
 1. Go to 'Oppia.org'
 2. Click on 'Translate text'
 3. Open network tab in inspector
 4. Select a language/ or click on the pagination icons
 4. See error

**Observed behavior**
Slow load times for translation opportunities

**Expected behavior**
Should have similar load times to question opportunities which are aprx 400 ms to 1 second vs 6- 7 seconds we are seeing here

**Screenshots**
![Screen Shot 2021-12-01 at 12 56 27 PM](https://user-images.githubusercontent.com/35385366/144312712-8388cf71-29a3-4000-83fa-0c93396f035d.png)






Functions to edit:
core/domain/opportunity_services.py:get_exp_opportunity_summary_with_in_review_translations_from_model
core/domain/opportunity_services.py:get_translation_opportunities
core/storage/suggestion/gae_models.py:GeneralSuggestionModel
Functions added: core/domain/opportunity_services.py:_build_exp_id_to_translation_suggestion_in_review_count
core/storage/suggestion/gae_models.py:GeneralSuggestionModel.get_in_review_translation_suggestions_by_exp_ids

Patch:
diff --git a/core/domain/opportunity_services.py b/core/domain/opportunity_services.py
index 71a8c378ca4c..5a0be0a9da4c 100644
--- a/core/domain/opportunity_services.py
+++ b/core/domain/opportunity_services.py
@@ -18,6 +18,7 @@
 
 from __future__ import annotations
 
+import collections
 import logging
 
 from core.constants import constants
@@ -25,6 +26,7 @@
 from core.domain import opportunity_domain
 from core.domain import question_fetchers
 from core.domain import story_fetchers
+from core.domain import suggestion_services
 from core.domain import topic_fetchers
 from core.platform import models
 
@@ -93,43 +95,6 @@ def get_exploration_opportunity_summary_from_model(model):
         {})
 
 
-def get_exp_opportunity_summary_with_in_review_translations_from_model(
-    model, translations_in_review):
-    """Returns the ExplorationOpportunitySummary object out of the model when
-    there are translations that are in review.
-
-    Args:
-        model: ExplorationOpportunitySummaryModel. The exploration opportunity
-            summary model.
-        translations_in_review: list(SuggestionModel). The list of translations
-            which are in review.
-
-    Returns:
-        ExplorationOpportunitySummary. The corresponding
-        ExplorationOpportunitySummary object.
-    """
-    translation_opportunity = get_exploration_opportunity_summary_from_model(
-        model)
-    translation_in_review_counts = {}
-
-    for language_code in constants.SUPPORTED_CONTENT_LANGUAGES:
-        in_review_count = 0
-        for suggestion in translations_in_review:
-            if (
-                suggestion is not None and
-                suggestion.language_code == language_code['code'] and
-                suggestion.target_id == model.id):
-                in_review_count = in_review_count + 1
-        if in_review_count > 0:
-            translation_in_review_counts[
-                language_code['code']] = in_review_count
-
-    translation_opportunity.translation_in_review_counts = (
-        translation_in_review_counts)
-
-    return translation_opportunity
-
-
 def _save_multi_exploration_opportunity_summary(
         exploration_opportunity_summary_list):
     """Stores multiple ExplorationOpportunitySummary into datastore as a
@@ -497,28 +462,55 @@ def get_translation_opportunities(language_code, topic_name, cursor):
         opportunity_models
         .ExplorationOpportunitySummaryModel.get_all_translation_opportunities(
             page_size, cursor, language_code, topic_name))
-    opportunities = []
-    suggestion_ids = []
-    opportunity_ids = []
-    translations_in_review = []
-    opportunity_ids = [
+    opportunity_summaries = []
+    opportunity_summary_exp_ids = [
         opportunity.id for opportunity in exp_opportunity_summary_models]
-    if len(opportunity_ids) > 0:
-        suggestion_ids = (
-            suggestion_models
-            .GeneralSuggestionModel
-            .get_translation_suggestions_in_review_ids_with_exp_id(
-                opportunity_ids))
-        translations_in_review = (
-            suggestion_models
-            .GeneralSuggestionModel
-            .get_multiple_suggestions_from_suggestion_ids(suggestion_ids))
+    exp_id_to_in_review_count = {}
+    if len(opportunity_summary_exp_ids) > 0:
+        exp_id_to_in_review_count = (
+            _build_exp_id_to_translation_suggestion_in_review_count(
+                opportunity_summary_exp_ids, language_code))
     for exp_opportunity_summary_model in exp_opportunity_summary_models:
-        exp_opportunity_summary = (
-            get_exp_opportunity_summary_with_in_review_translations_from_model(
-                exp_opportunity_summary_model, translations_in_review))
-        opportunities.append(exp_opportunity_summary)
-    return opportunities, cursor, more
+        opportunity_summary = (
+            get_exploration_opportunity_summary_from_model(
+                exp_opportunity_summary_model))
+        if opportunity_summary.id in exp_id_to_in_review_count:
+            # Compute the translation_in_review_counts domain object field
+            # adhoc. Note that this field is not persisted and is only used in
+            # the frontend.
+            # TODO(#14833): Compute this value in the backend controller
+            # instead.
+            opportunity_summary.translation_in_review_counts = {
+                language_code: exp_id_to_in_review_count[opportunity_summary.id]
+            }
+        opportunity_summaries.append(opportunity_summary)
+    return opportunity_summaries, cursor, more
+
+
+def _build_exp_id_to_translation_suggestion_in_review_count(
+        exp_ids, language_code):
+    """Returns a dict mapping exploration ID to the count of corresponding
+    translation suggestions that are currently in review.
+
+    Args:
+        exp_ids: list(str). List of exploration IDs for which to count
+            corresponding translations suggestions.
+        language_code: str. The language for which translation suggestions
+            should be fetched.
+
+    Returns:
+        dict(str, int). Dict of exploration IDs to counts of corresponding
+        translation suggestions currently in review.
+    """
+    exp_id_to_in_review_count = collections.defaultdict(int)
+    suggestions_in_review = (
+        suggestion_services
+        .get_translation_suggestions_in_review_by_exp_ids(
+            exp_ids, language_code))
+    for suggestion in suggestions_in_review:
+        if suggestion is not None:
+            exp_id_to_in_review_count[suggestion.target_id] += 1
+    return exp_id_to_in_review_count
 
 
 def get_voiceover_opportunities(language_code, cursor):
diff --git a/core/domain/suggestion_services.py b/core/domain/suggestion_services.py
index a758fe622a5c..9c49486a6b3b 100644
--- a/core/domain/suggestion_services.py
+++ b/core/domain/suggestion_services.py
@@ -680,6 +680,31 @@ def get_translation_suggestions_in_review_by_exploration(exp_id, language_code):
     ]
 
 
+def get_translation_suggestions_in_review_by_exp_ids(exp_ids, language_code):
+    """Returns translation suggestions in review by exploration ID and language
+    code.
+
+    Args:
+        exp_ids: str. Exploration IDs matching the target ID of the
+            translation suggestions.
+        language_code: str. The ISO 639-1 language code of the translation
+            suggestions.
+
+    Returns:
+        list(Suggestion). A list of translation suggestions in review with
+        target_id in exp_ids and language_code == language_code.
+    """
+    suggestion_models_in_review = (
+        suggestion_models.GeneralSuggestionModel
+        .get_in_review_translation_suggestions_by_exp_ids(
+            exp_ids, language_code)
+    )
+    return [
+        get_suggestion_from_model(model) if model else None
+        for model in suggestion_models_in_review
+    ]
+
+
 def _get_plain_text_from_html_content_string(html_content_string):
     """Retrieves the plain text from the given html content string. RTE element
     occurrences in the html are replaced by their corresponding rte component
diff --git a/core/storage/suggestion/gae_models.py b/core/storage/suggestion/gae_models.py
index cdd00eccb427..fa7917085d25 100644
--- a/core/storage/suggestion/gae_models.py
+++ b/core/storage/suggestion/gae_models.py
@@ -485,6 +485,30 @@ def get_in_review_translation_suggestions(
             cls.language_code.IN(language_codes)
         )).fetch(feconf.DEFAULT_SUGGESTION_QUERY_LIMIT)
 
+    @classmethod
+    def get_in_review_translation_suggestions_by_exp_ids(
+        cls, exp_ids: List[str], language_code: str
+    ) -> Sequence[GeneralSuggestionModel]:
+        """Gets all in-review translation suggestions matching the supplied
+        exp_ids and language_code.
+
+        Args:
+            exp_ids: list(str). Exploration IDs matching the target ID of the
+                translation suggestions.
+            language_code: str. The ISO 639-1 language code of the translation
+                suggestions.
+
+        Returns:
+            list(SuggestionModel). A list of suggestions matching the supplied
+            exp_ids and language_code.
+        """
+        return cls.get_all().filter(datastore_services.all_of(
+            cls.status == STATUS_IN_REVIEW,
+            cls.suggestion_type == feconf.SUGGESTION_TYPE_TRANSLATE_CONTENT,
+            cls.target_id.IN(exp_ids),
+            cls.language_code == language_code
+        )).fetch(feconf.DEFAULT_SUGGESTION_QUERY_LIMIT)
+
     @classmethod
     def get_in_review_question_suggestions(
         cls, user_id: str
diff --git a/core/templates/pages/contributor-dashboard-page/opportunities-list-item/opportunities-list-item.component.html b/core/templates/pages/contributor-dashboard-page/opportunities-list-item/opportunities-list-item.component.html
index bcf8aa7073f9..96e213bf9f3a 100644
--- a/core/templates/pages/contributor-dashboard-page/opportunities-list-item/opportunities-list-item.component.html
+++ b/core/templates/pages/contributor-dashboard-page/opportunities-list-item/opportunities-list-item.component.html
@@ -22,7 +22,7 @@
       <div [hidden]="opportunityDataIsLoading"  *ngIf="translationProgressBar" class="progress">
         <div class="progress-bar bg-success" role="progressbar" [ngStyle]="translatedProgressStyle" ngbTooltip="{{opportunity.translationsCount}}/{{opportunity.totalCount}} cards accepted" container="body" placement="bottom"></div>
         <div class="progress-bar bg-light-green" role="progressbar" [ngStyle]="inReviewProgressStyle" ngbTooltip="{{opportunity.inReviewCount}}/{{opportunity.totalCount}} cards awaiting review" container="body" placement="bottom"></div>
-        <div class="progress-bar bg-light" role="progressbar" [ngStyle]="untranslatedProgressStyle" ngbTooltip="{{opportunity.totalCount - (opportunity.translationsCount + opportunity.inReviewCount)}}/{{opportunity.totalCount}} cards untranslated" container="body" placement="bottom"></div>
+        <div class="progress-bar bg-light" role="progressbar" [ngStyle]="untranslatedProgressStyle" ngbTooltip="{{opportunity.totalCount - (opportunity.translationsCount + opportunity.inReviewCount)}}/{{opportunity.totalCount}} cards available for translation" container="body" placement="bottom"></div>
       </div>
       <lazy-loading [hidden]="!opportunityDataIsLoading"></lazy-loading>
     </div>
@@ -31,7 +31,7 @@
             *ngIf="translationProgressBar"
             [hidden]="opportunityDataIsLoading"
             [disableTooltip]="!translationProgressBar"
-            ngbTooltip="Percentage of accepted translated cards. {{opportunity.totalCount - (opportunity.translationsCount + opportunity.inReviewCount)}}/{{opportunity.totalCount}} cards remaining."
+            ngbTooltip="Percentage of accepted translated cards"
             placement="bottom">
         ({{ progressPercentage }})
       </span>

Test patch: diff --git a/core/domain/opportunity_services_test.py b/core/domain/opportunity_services_test.py
index e09248ae769b..680026817561 100644
--- a/core/domain/opportunity_services_test.py
+++ b/core/domain/opportunity_services_test.py
@@ -191,35 +191,33 @@ def test_get_translation_opportunities_with_translations_in_review(
             opportunity_services.get_translation_opportunities(
                 'hi', 'topic', None))
         self.assertEqual(len(translation_opportunities), 0)
-
         self.add_exploration_0_to_story()
         self.create_translation_suggestion_for_exploration_0_and_verify()
 
         translation_opportunities, _, _ = (
             opportunity_services.get_translation_opportunities(
                 'hi', 'topic', None))
+
         self.assertEqual(len(translation_opportunities), 1)
         opportunity = translation_opportunities[0]
-        languages_of_translations_in_review = (
-            opportunity.translation_in_review_counts.keys())
-        self.assertEqual(len(languages_of_translations_in_review), 1)
+        self.assertEqual(
+            opportunity.translation_in_review_counts,
+            {'hi': 1})
 
     def test_get_translation_opportunities_with_no_translations_in_review(self):
         translation_opportunities, _, _ = (
             opportunity_services.get_translation_opportunities(
                 'hi', 'topic', None))
         self.assertEqual(len(translation_opportunities), 0)
-
         self.add_exploration_0_to_story()
 
         translation_opportunities, _, _ = (
             opportunity_services.get_translation_opportunities(
                 'hi', 'topic', None))
+
         self.assertEqual(len(translation_opportunities), 1)
         opportunity = translation_opportunities[0]
-        languages_of_translations_in_review = (
-            opportunity.translation_in_review_counts.keys())
-        self.assertEqual(len(languages_of_translations_in_review), 0)
+        self.assertEqual(opportunity.translation_in_review_counts, {})
 
     def test_opportunity_get_deleted_with_removing_exploration_from_story_node(
             self):