I have a very large database. I’m using it in MariaDB on my staging server but just to give you an example of how large we’re talking, the SQLite file on my dev box is 242 GB. It’s all searchable text data.
My Xojo web app on the server is an API so I’m doing all the work in HandleURL. Pretty much all my endpoints involve the database, and queries can take anywhere from 500ms to 10000ms to return a result (searches take the longest but are necessary functionality). Unfortunately this seems to hang up the main thread of the web app and it is unresponsive during this time to other queries.
As an experiment, I attempted a ticket/checkup system where the API returns a job ID and the client checks back on the result later. Unfortunately the web app is still unresponsive while waiting for the database, while the app is “doing the work.” A ticket/checkup system is not ideal for my use case so I’d rather avoid it if I could.
I noticed the same behavior when shelling to mysql on the command line as well. Shells seem to block everything too so that was not a viable workaround.
I’m not sure how to do this without putting my database on the open internet. Here is a sample project showing the same behavior using a shell.
Click the “run” button on the left and try to load the “time” endpoint on the right while it’s still waiting. They will both return at the same time, once the wait has completed.
Thanks, yeah, this seems like the only way to make this work in Xojo. The ticket/response adds a layer of complexity to both server and client that I really don’t want to deal with, so I might end up switching to php for this.
Other requests come in on co-operative threads. As web is a framework on top of console, we know we need DoEvents to perform the other tasks while we wait.
With just a while loop in HandleURL it will lock up waiting, which is the original problem with extra steps.
Here’s an updated version of the Shell example that lets you switch between synchronous and asynchronous shells for the wait.
Async shells use the while / doevents / wend loop and are indeed responsive to new connections. This is really more of an academic exercise because I am not eager to deploy something that uses DoEvents or has such a long http response time.
Interestingly enough, in the screenshot (or if you run the demo for yourself), you can see that the Query number (3 in the screenshot) is the same on both the async shell connection and the time connection. This is a great illustration of why this approach is so dangerous. The querycount is a private integer on the app class. The /wait call was QueryCount=2 and while the shell was executing it was incremented to 3 during the /time call. After the shell completed the HandleURL thread resumed, reading the new QueryCount value of 3. If you’re not expecting this, you can introduce some weird bugs in your code that are really difficult to track down (the fix in this case would be to read the QueryCount value into a local variable in the HandleWait method prior to executing the shell).
