-
Improvement
-
Resolution: Fixed
-
Minor
-
Future Dev
-
MOODLE_405_STABLE
-
MDL-78030-main-improvement -
Setup requirements
To ease the test, please use Moodle-docker. The improvement will be tested with PostgreSQL, MariaDB, Oracle and MySQL.
Why testing MySQL?
When there is a list with pagination using the get_counted_records_sql() or get_counted_recordset_sql(), then it will generate a new SQL query whether the database "supports" the count window functions, and we want to make sure that the query is executed without any errors and present the same data across the supported database engines.
MySQL and MSSQL didn't support the count window function due to its performance, but we still need to test them to ensure the data are present correctly and the is_count_window_function_supported() function works properly.
Unit Testing
Run the below command:
vendor/bin/phpunit --filter="test_count_window_function"
Manual Testing 1: Course participants
- Login as an administrator.
- Go to Site Administration > Development > Make Test Course.
- Choose "M" from the Size of Course drop-down menu.
- Specify the coursename with "Test Course M".
- Click on the Create Course button.
- Navigate to the course page.
- Click the Participants
- Verify that the participant list is identical to the list without the patch.
- It's ok if they are ordered differently.
- Verify that the ordering column has the same result as without the patch.
- Verify that the pagination is working without any errors and the result is the same as without the patch
Manual Testing 2: Language customisation
- Login as an administrator
- Go to Site Administration > General > Language Customisation
- Select "English (en)" in the dropdown
- Click the "Open language pack for editing" button
- Wait until the progress is complete.
- Click the "Continue" button
- Select "moodle.php" in the "Show strings of these components"
- Click the "Show strings" button
- Verify that the list is identical to the list without the patch.
- Verify that all the filters work correctly and the list is identical to the list without the patch.
- Verify that the pagination is working without any errors and the list is identical to the list without the patch.
Note:
Please repeat all the tests with different database engines (PostgreSQL, MariaDB, Oracle and MySQL).CLI for Benchmarking
- Do not apply the patch
- Copy the scripts benchmark.php
to the admin/cli directory. - Running the below commands:
bin/moodle-docker-compose exec webserver php admin/cli/benchmark.php -u=10000 -t=10
- After completing the process, note the Average time (Avg Time).
- Please repeat all the steps above with different database engines (PostgreSQL, MariaDB, Oracle).
- Apply the patch
- Running the below commands:
bin/moodle-docker-compose exec webserver php admin/cli/benchmark.php -u=10000 -t=10
- After completing the process, note the Average time (Avg Time).
- Please repeat all the steps above with different database engines (PostgreSQL, MariaDB, Oracle).
- Compare the AVG time before and after the patch between the database engines.
- VERIFY that the result with the patch has a lower average than without the patch.
Setup requirements To ease the test, please use Moodle-docker . The improvement will be tested with PostgreSQL, MariaDB, Oracle and MySQL. Why testing MySQL? When there is a list with pagination using the get_counted_records_sql() or get_counted_recordset_sql(), then it will generate a new SQL query whether the database "supports" the count window functions, and we want to make sure that the query is executed without any errors and present the same data across the supported database engines. MySQL and MSSQL didn't support the count window function due to its performance, but we still need to test them to ensure the data are present correctly and the is_count_window_function_supported() function works properly. Unit Testing Run the below command: vendor/bin/phpunit --filter= "test_count_window_function" Manual Testing 1: Course participants Login as an administrator. Go to Site Administration > Development > Make Test Course. Choose "M" from the Size of Course drop-down menu. Specify the coursename with "Test Course M". Click on the Create Course button. Navigate to the course page. Click the Participants Verify that the participant list is identical to the list without the patch. It's ok if they are ordered differently. Verify that the ordering column has the same result as without the patch. Verify that the pagination is working without any errors and the result is the same as without the patch Manual Testing 2: Language customisation Login as an administrator Go to Site Administration > General > Language Customisation Select "English (en)" in the dropdown Click the "Open language pack for editing" button Wait until the progress is complete. Click the "Continue" button Select "moodle.php" in the "Show strings of these components" Click the "Show strings" button Verify that the list is identical to the list without the patch. Verify that all the filters work correctly and the list is identical to the list without the patch. Verify that the pagination is working without any errors and the list is identical to the list without the patch. Note: Please repeat all the tests with different database engines (PostgreSQL, MariaDB, Oracle and MySQL). CLI for Benchmarking Do not apply the patch Copy the scripts benchmark.php to the admin/cli directory. Running the below commands: bin/moodle-docker-compose exec webserver php admin/cli/benchmark.php -u= 10000 -t= 10 After completing the process, note the Average time (Avg Time). Please repeat all the steps above with different database engines (PostgreSQL, MariaDB, Oracle). Apply the patch Running the below commands: bin/moodle-docker-compose exec webserver php admin/cli/benchmark.php -u= 10000 -t= 10 After completing the process, note the Average time (Avg Time). Please repeat all the steps above with different database engines (PostgreSQL, MariaDB, Oracle). Compare the AVG time before and after the patch between the database engines. VERIFY that the result with the patch has a lower average than without the patch.
-
6
-
Team Hedgehog 2023 Sprint 4.3, Team Hedgehog 2023 Review 4, Team Hedgehog 2024 Sprint 1.1, Team Hedgehog 2024 Sprint 1.2, Team Hedgehog 2024 Sprint 1.3, Team Hedgehog 2024 Sprint 1.4, Team Hedgehog 2024 Review 1, Team Hedgehog 2024 Sprint 2.1, Team Hedgehog 2024 Sprint 2.2, Team Hedgehog 2024 Sprint 2.3, Team Hedgehog 2024 Review 2
If you have any sufficiently large data set the query behind anything in tablelib and several places where reports are used, operations will get slower. When viewing the report it grabs a page of data, and it also grabs the total count of records without the pagination. Both of these raw queries take effectively the same amount of time to run which doubles the report time and db load.
Using window functions both of these can be combined into a single query for an easy 2x performance win across the board in all reports with no changes to any of the reports which is a pretty rare win 
So for a query like this:
SELECT username
|
FROM {user}
|
WHERE username LIKE '%gmail%'
|
we can auto wrap it like this:
SELECT results.*, COUNT(*) OVER() AS full_count FROM (
|
SELECT username
|
FROM {user}
|
WHERE username LIKE '%gmail%'
|
) results
|
This takes no appreciable difference from the original query.
A more detailed example with some edge cases to cover is here:
Window function support should be fairly good across all supported DB's and we can fall back to existing count where it isn't.
