Repository: oppia/oppia
Issue category: Performance Issue

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)






Hints:
Seeing the same issue for displaying reviewable suggestions, which is probably due to fetching all available reviewable suggestions up to the query limit of 1000 in core/storage/suggestion/gae_models.py. 
@sagangwee Hmm, It's strange when i tested this change I had about 200 suggestions, and it seemed to display pretty quickly. Can you post a screenshot of the network tab when loading the reviewable suggestions.

Sure, thing:

<img width="762" alt="Screen Shot 2021-12-16 at 5 58 27 PM" src="https://user-images.githubusercontent.com/9004726/146461417-fd1c09bc-d0d2-4386-bb38-458db5c12c7a.png">

That's for what I believe is the max 1000 suggestions.


The slow translation opportunity load times are probably due to the translations in review calculation in [opportunity_services.py.get_exp_opportunity_summary_with_in_review_translations_from_model](https://github.com/oppia/oppia/blame/develop/core/domain/opportunity_services.py#L96)
since we are iterating through all the translations_in_review for every opportunity. Original change in https://github.com/oppia/oppia/pull/13835.

Specifically, we:
1. Fetch the IDs of all translation suggestions in review matching the opportunity IDs.
2. Fetch all suggestions from the IDs found in 1.
3. For every opportunity, iterate through all the suggestions from 2 and update the opportunity's in review counts.

Optimizations:
1. We can probably combine the suggestion fetches in 1 and 2 into on call. We also only care about the current language code, so we can apply a filter on language as well.
2. Instead of iterating through all the current in review suggestions for every opportunity in step 3, we can iterate through the suggestions and create a mapping of exploration ID -> in review suggestion count and use the mapping to update the opportunities.
Will fix as part of https://github.com/oppia/oppia/issues/7571.

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):