Performance Improvements

This update is mostly about making CompletionLab faster. After 2.0 launched, I noticed that some pages were taking quite a bit longer to load than they should have, particularly Profiles, Leaderboards, and the Games browser. I spent some time digging into it and found a couple of interesting problems. Database Performance The biggest issue was actually pretty simple. SQLite keeps statistics about the data it stores, which it uses to decide how to run queries efficiently. The problem was that CompletionLab’s statistics hadn’t been properly refreshed since September. At the time, the user-achievement table had around 70,000 records. By the time 2.0 launched, that had grown to almost 185,000. The automatic maintenance was running, but it wasn’t refreshing those statistics because the growth hadn’t crossed its threshold. So instead of looking through one player’s achievements to find their latest unlocks, the database was sometimes going through practically the entire game catalogue. All that work just to find four achievements. Wonderful. :) Refreshing the statistics made a pretty substantial difference in testing, and this update also improves how they’re maintained so the same problem doesn’t gradually come back. Unnecessary Queries I also found several places where pages were requesting the same information multiple times. Some profile data was being calculated three times per visit, and parts of the Games browser were doing similar work. There’s really no reason to ask the database for the same answer over and over when we can calculate it once and reuse it. This update reduces that duplicated work and improves the queries behind Profiles, Games, and Leaderboards. More to Come I’m going to keep looking for these kinds of problems as CompletionLab grows. Not every update needs to introduce something new. Sometimes I’d much rather make the stuff that’s already there work properly. And waiting three seconds for a profile to load isn’t something I intend to keep around. :)