Plugin slows down course overview --> cache needed

[schuettloeffel-elsa]

I did some research on loading times for the meeting plugin and found out that the calculation for getIconNavigation() is quite slow. Main Problem is the fetching of all meetings for a specific course from the database in MeetingCourse::findActiveByCourseId().

First of all, the query is pretty slow since it has to sort a an unindexed column --> maybe an index for vc_meetings.name should be added and the ORDER BY m.name + 0, m.name should be changed to ORDER BY m.name for a faster sorting.

Second, even if the query is optimised, it takes ~0.015 seconds to execute on our systems (the table is quite large with 34k entries). Having e.g. 8 different courses in the course overview adds up to 100ms waiting time just for these queries, and most students have way more than 8 courses in the list. What to do? Maybe implement a cache:

diff --git a/MeetingPlugin.php b/MeetingPlugin.php
index 4b3d432..459bb56 100644
--- a/MeetingPlugin.php
+++ b/MeetingPlugin.php
@@ -167,10 +167,19 @@ class MeetingPlugin extends StudIPPlugin implements PortalPlugin, StandardPlugin
         /** @var Seminar_Perm $perm */
         $perm = $GLOBALS['perm'];
 
+        $cache = StudipCacheFactory::getCache();
         if ($perm->have_studip_perm('tutor', $courseId)) {
-            $courses = MeetingCourse::findByCourseId($courseId);
+            $courses = unserialize($cache->read('plugins/MeetingPlugin/MeetingCourse/' . $courseId));
+            if (empty($courses)) {
+                $courses = MeetingCourse::findByCourseId($courseId);
+                $cache->write('plugins/MeetingPlugin/MeetingCourse/' . $courseId, serialize($courses), 3600);
+            }
         } else {
-            $courses = MeetingCourse::findActiveByCourseId($courseId);
+            $courses = unserialize($cache->read('plugins/MeetingPlugin/MeetingCourse/active/' . $courseId));
+            if (empty($courses)) {
+                $courses = MeetingCourse::findActiveByCourseId($courseId);
+                $cache->write('plugins/MeetingPlugin/MeetingCourse/active/' . $courseId, serialize($courses), 3600);
+            }
         }

--> having that cache improved the response time for the course overview by 100ms (from 230ms to 130ms) on our test systems!

[ferishili]

Hi @schuettloeffel-elsa
Thanks for your contribution.
Just for your info: You could have made a PR for this enhancement.
The part for optimizing the query for sorting, I would prefer not to change it for now.

Best Regards

[schuettloeffel-elsa]

Allright, next time :)

[ferishili]

In addition to the cache, the index is also added to that table, so there would solve the performance issue very well.