My work recently upgraded from Moodle 1.9 to 2.3, and some users were experiencing slow page loads while the site was zippy for others. Today we discovered that, for some users, the My Moodle dashboard page was requiring several thousands of DB queries. For one user, enrolled in four courses, the page required over 14,000 queries. I guess it could be worse: one user reported over 95,000!

This was completely unacceptable; this page is a common navigation point for all users, and every user is forwarded there on login.

With xdebug and Autosalvage rocking, it only took me about three hours to rework the page so that only the course links are displayed by default, and a button is provided beside each to load that course’s activities into the page via Ajax. Since most users just use the page for navigation between courses, this tradeoff seems well worth the performance gain. Now this page–without displaying activities–is down to ~60 queries for every user (sadly average for a Moodle page).

I suspect that loading the activity list for a large course will still take a performance bite, but in my limited testing it seemed pretty instantaneous–yes, there’s a reason why modern apps are built on Ajax. Although good work has been done to cache front-end files, Moodle still seems to be in serious need of query reduction optimization when building HTML pages.

After getting some feedback over the weekend, I’ll release this patch for other Moodle providers. Our theme uses jQuery and the Javascript side of this was maybe 10 lines, but I imagine it would need to be ported to YUI to get into core.