This update entailed several changes to the LonelyCache database that had to be performed while the site was offline. My priority was on correctness rather than quickness so I apologize for any inconvenience you may have had while the site was down.
As with the last update, there is no change to the look and feel of the site. The changes were to improve site performance and to get us closer to being able to expand the LonelyCache territory.
The updates are:
- Previously, when the site was updated, the new set of statistics were kept in the same database table as the active set (or the old set once the new set became the active set
). This was fine and worked well when the statistics were not being updated however when the statistics were being updated, timeouts would occur attempting to query the active set. These timeouts were due to all the change activity occurring on the table. Now we have two identical tables (except for their names) instead of one. One table will contain the active set and the other will contain the old set or the new set that is being created. We also have a view into the two tables that will select the table that contains the active set. The upshot of all this is that the site users should no longer see timeouts (empty parts of web pages) when the statistics are being updated (normally 2-4 AM Eastern time). - With several of the queries we combine two sets of results, one for caches found and the other for caches hidden. I had naively used the UNION database operation instead of UNION ALL. UNION basically sorts and merges the two sets, removing any duplicates while UNION ALL just combines the two sets without any sorting and duplicate removal. In my testing, I saw vast performance improvements when creating the Combined Points leaderboards and the Backcountry leaderboards. I'm keeping my fingers crossed that this will resolve the problems the site sees periodically with the nightly statistics update not completing in a timely manner and I'm really curious to see what time the nightly update completes tomorrow.
Russell
