Hi, i have a web app that in some report do a long query to a remote mysql database. The query take like 30 seconds. The problem is that if one user run this report all other users experiment the UI freeze until the first report is generated. I try playing with webthread because i need to access the same session database connection, i move all the query to a web thread but i no see change, same problem, all users need to wait until first report is finished. I know that duplicating the app resolve the problem but i just look for programming solution for better use of the resources. Any recommendation?
Without seeing your code, I will venture guessing you are using one or more loops to poll the database until the query comes back. Tight loops do freeze the UI because they consume significant CPU power.
You want to replace loops by timers, which allows you to poll at larger intervals (a loop typically runs at less than one millisecond).
Typical loop :
For i as integer = 1 to 1000
//do something
next i
Using a timer Action event. i as integer has been added to the WebPage. Default value 1 in this example.
if i <= 1000 then
//do something
i = i + 1
else
me.mode = Timer.ModeOff
i = 1
end if
To start the timer, do
Timer1.Mode = Timer.ModeMultipleThe added interest of proceeding that way is that your UI will remain responsive without a thread.
Since the processing time is rather long, you may use a period of 500 milliseconds. That will release even more CPU for other tasks and users.
Another solution (if it really is the query itself) is to put the query code into a console app which communicates with the main app through an IPCSocket or other means. The advantage being that it will run in another process and probably on another core in a multi-core system.
My query is two procedure in database side that perform a lot of queries to return a report. The problem is why other users with other sessions open in the same web app experiment UI freeze if everyone have a independent database connection session base. And also i request the query from a web thread.
I would load balance your app. Long running queries using most database plugins are blocking and no amount of threading or web threading will resolve the issue.
Whatever. The best solution I think is having a helper. That way the main app remains responsive. Problem will raise if two users request a report at the same time.
Make sure the query code ultimately produces a PDF or report of some kind. If the query takes 30 seconds and it returns a lot of rows you don’t want to return the RecordSet back to the web app. It would have to serialize the entire recordset into JSON/XML/whatever to go over the IPCSocket. That’s just wasting cycles and still gonna end up in blocks anyway as the web app deserializes it and loops through it.
Other downside to helper apps like Michel mentioned is on Linux you can only run instance of a Xojo app at a time. So two users requesting a report kills your app.
The easiest solution without re-architecting your app is just load balancing it because then you can scale up the number of users with more instances so one users long report does not impact others.
If you are going to go the helper route you ultimately need a server. You need something that knows how to produce reports and has a queue. Then you can pass the variables to it to generate a report and its constantly working through its queue. It can then notify the web app that report #1234 is complete.
Yes, Phillip is right. However, if you use the Monkeybread SQL plugin, the other sessions will not be blocked (in any case, with SQLITE).
(https://xojo.com/issue/42544)>]<https://xojo.com/issue/42544>I have the same problem with SQLITE:
This is an important bug. It’s really not normal that such an important bug has not been fixed for 14 months (While the solution was given by Richard Hipp, and that Christian corrected it immediately in his monkeybread SQL plugin).
Thank you all for the information. Oliver i goin to try the Monkeybread SQL plugin. Thanks!!