Monday, June 12, 2023

Another database woes update....

Photo provided by Essex Explorers

Yes, I continue working on the problem. Yes, it still is a problem. Opening logbooks for premium members has definitely caused a lot of slow queries which I haven't been able to put in adequate hacks for, which is why logbooks are still closed to non-premium members. (I'm half tempted to close them again for premium members, but since it doesn't seem to actually be crashing the database, I'm leaving it open.)

Today, I reached another major milestone in my Next Big Update--which is primarily about making database queries faster and more efficient. I've updated the core subsystem that runs searches on my development machine, and it seems to work pretty well over all. Of course, just because it works well on my development machine doesn't guarantee the same on the live website. *knock on wood*

But this is a huge milestone, and completely rewrites how AQ queries the database when  you run a search. In some ways, the queries are simpler. In other ways, they're actually more complex. I use a lot more subqueries now, breaking big queries into smaller ones that are easier for the database to execute, but then the subqueries get merged together which adds complexity.

But most of you probably aren't interested in those sorts of nitty gritty details. Y'all want to know.... when will everything be back to normal?!

And... I'm still not sure. I just finished getting the core search queries done, but I still need to create unit tests and thoroughly kick the tires to make sure it all works as expected. And there are "mini-searches" scattered all over AQ that need to be updated all over the place. For example, a lot of the widgets on My Page are just the results of regular searches that can be performed manually, so all of those will need to be updated (and subsequently tested). Heck, even the logbooks are basically just specialized searches. All that code is connected.

So there's still a lot of work to be done... .but progress continues and at this point, I feel optimistic that I might be able to install a permanent fix and update AQ in... another month? Well, okay, a month might be optimistic, but theoretically possible. Two months might be more realistic.

Yeah, I know, like I said... there's still so much for me to test, fix, and make sure the update goes as smoothly as possible. I was reluctant to even give estimates before because I really couldn't even fathom how long this might take, but now that the core code has been updated and seems to work reasonably well, I feel like I have a much better insight into how long it'll take me to properly test and fix the new code and integrate it into the rest of AQ.

No comments: