Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-77240

Performance issue due to double use of a subquery

XMLWordPrintable

    • MOODLE_401_STABLE, MOODLE_402_STABLE
    • MDL-77240_Avoid_using_subqueries-MOODLE_401_STABLE
    • MDL-77240_Avoid_using_subqueries-master
    • Hide

      Unittest

      The changes are covered by numerous existing unit tests. The patch only offers an optimisation of existing code and does not include any new functionalities.

      Run the whole Unit-Test-Suite

      UI test:
      Create a new course with multiple activities
      Work in the activities
      Delete course

      Expectations of the Surface Test
      The course will be deleted.

      To test the Performance Impact

      Especially for very large instances, with 100,000+ courses and several million activities, this optimization leads to a significant performance increase. I.e. for tests a large number of courses with activities would have to be created. For small instances, the optimization is hardly measurable.

      • Create 20.000 Courses with 10 activities each. 
      • Create 100.000 Users and enrol 50 of them to each course.
      • Create also some activity/course completions -> work in several, ideal in all courses with all users.
      • Delete one course in synchroneous (traditional) mode.
      • Delete one course in background mode.

      Expectation:

      The synchronous delete takes a very long time, and exceeds the server timeout (2 minutes) in some cases.

      The async deletion works.

      Show
      Unittest The changes are covered by numerous existing unit tests. The patch only offers an optimisation of existing code and does not include any new functionalities. Run the whole Unit-Test-Suite UI test: Create a new course with multiple activities Work in the activities Delete course Expectations of the Surface Test The course will be deleted. To test the Performance Impact Especially for very large instances, with 100,000+ courses and several million activities, this optimization leads to a significant performance increase. I.e. for tests a large number of courses with activities would have to be created. For small instances, the optimization is hardly measurable. Create 20.000 Courses with 10 activities each.  Create 100.000 Users and enrol 50 of them to each course. Create also some activity/course completions -> work in several, ideal in all courses with all users. Delete one course in synchroneous (traditional) mode. Delete one course in background mode. Expectation: The synchronous delete takes a very long time, and exceeds the server timeout (2 minutes) in some cases. The async deletion works.
    • 10
    • Team Hedgehog 2024 Review 1, Team Hedgehog 2024 Sprint 2.1

      We run a very large moodle instance with about 14 million activities in about 400,000 courses. More than 50,000 teachers manage their courses independently. Here we noticed that the queries

      $DB->delete_records_subquery('course_modules_completion', 'coursemoduleid', 'id','SELECT id from {course_modules} WHERE course = ?', [$courseid]);
       
      $DB->delete_records_subquery('course_modules_viewed', 'coursemoduleid', 'id','SELECT id from {course_modules} WHERE course = ?', [$courseid]); 

      in lib/moodlelib.php  run extremely frequently and therefore for a relatively long time.

      MDL-58266 was the first to add the second query.

      The use of sub-queries and especially of two identical sub-queries should be avoided for performance reasons.

            raquel.ortega@moodle.com Raquel Ortega
            MPe Peter Mayer
            Jordi Pujol-Ahulló Jordi Pujol-Ahulló
            Jake Dallimore Jake Dallimore
            Votes:
            0 Vote for this issue
            Watchers:
            18 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 day, 50 minutes
                1d 50m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.