[RM-31417]: <THNCA> นำตัวเลขจำนวนข้อสอบในระบบคลังข้อสอบกลับมาได้ไหม ทำไมระบบ elearning ไม่ช้า แต่ในระบบ Exam ช้า

Viewed 3
elearning thnca exam moodle

Problem Summary

From Redmine:

Root Cause Analysis

The slowness was caused by the get_categories_for_contexts function in qbank_managecategories\helper. This function used a Correlated Subquery inside the main SELECT statement to calculate the questioncount for each category.

This means for every single category row returned, a complex subquery was executed again. If there were 1,000 categories, the subquery would run 1,000 times, leading to extremely poor performance.

The original problematic SQL looked something like this:

SELECT c.*,
    (SELECT COUNT(1) ... WHERE ... c.id = qbe.questioncategoryid ...) AS questioncount
  FROM {question_categories} c
 WHERE ...
1 Answers

Solution / Workaround

The query was refactored to eliminate the correlated subquery. The new approach uses a Common Table Expression (CTE) to pre-calculate the question counts for all categories in a single, efficient operation. The main query then performs a simple LEFT JOIN on these pre-calculated results.

This change reduces thousands of small, repetitive queries into one single, optimized query.

For finding the latest version of a question, the slow nested SELECT MAX(version) was replaced with the highly efficient ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...) window function, which is standard practice for this type of problem and works across different database systems.

The changes were applied to the get_categories_for_contexts function in qbank_managecategories\helper.

    public static function get_categories_for_contexts(
        string $contexts,
        string $sortorder = 'parent, sortorder, name ASC',
        bool $top = false,
        int $showallversions = 0,
    ): array { /* ... implementation changed ... */ }

Upstream version https://github.com/moodle/moodle/blob/MOODLE_405_STABLE/question/bank/managecategories/classes/helper.php#L315
New version customers/thnca/exam-course-category/helper.php

Verification Steps

  1. Go to Edit course categories.
  2. Click Add category.
  3. You will see modal ASAP.
happy1
Related