Hi all,
Doing a lot of research on this, I want to run an sql query in a thread so as to keep the UI responsive.
It will be run from the text changed event in a textbox. The thread will execute an sql query (postgresql on the internet) then a timer will check for results back on the main UI.
I have tested this and it does not work. Text input is still very sluggish. It seems to have made no difference, still waiting for the query to finish before the UI free’s up.
Reading other forum posts, I have concluded that the database plugin is accessing the lib files for xojo and thus lock the UI regardless of being run in a thread.
Is this correct? And if so, will writing a console app to do the query change anything as it would be accessing the same lib files?
Imusing xojo 2014r3.2
Well, if I remember correctly all the queries in Xojo database plugins from Xojo Inc. have been modified some time ago to run work in thread, so the xojo thread can give time to GUI thread.
On the other side, MBS SQL Plugin offers an alternative including executing SQL statements via Preemptive Threading, so the Xojo thread can yield time and GUI keeps responsive.
Please be aware that running a query in thread while keeping the UI responsive will only work when not using Prepared Statements. See <https://xojo.com/issue/37806>
Thanks Christian. I have read that before, not sure why its not working then.
really simple code of:
in the textbox.textchanged event:
if thread1.state <> 4 then
thread1.kill
end if
thread1.run
in the thread1.run event:
dim rs as recordset
rs = database1.sqlSelect( sqlquery )
Not displaying results yet, trying to keep it simple to debug
Theoretically, user should be able to type without delay. But user input is stalled until the query has completed.
So if user types ‘a’ the box locks up until the query has finished, then user can continue to type ( but when they do, it stalls again.
This is the same behaviour as running the sql query directly from the textchanged event.
Dim sql as string
Dim rs as RecordSet
sql = “SELECT ID, LastName, FirstName, Suburb, Postcode FROM Customer WHERE ID = '” + search term + “’”
rs = database1.sqlSelect( sql )
ok, same app, same code, just replaced postgresql database with mysqlcommunityserver
It runs better, but still has a lag. I think it runs better, because the host is faster, not to do with anything else.
But still, same issue.
I do sql queries in a thread and write the results to a dictionary, then use a timer to write the dictionary content to a listbox. The timer checks the thread state and waits until the thread has finished running before it writes the query results. I use a progress bar to indicate the progress of the process.
@Carl Hogue Thanks, but i am searching after every keystroke, not once a full name is entered. The query is actually quite fast, only a second or 2, but between keystrokes, this feels like watching a high def video on a computer made in the 70’s, jumpy and jerky.
@Christian Schmitz Did you get a chance to look at my project? I have been looking at the MBS plugins for a while now. Haven’t had a real need for them as of yet (enough to warrant a purchase), but if this sql plugin can make my test project work correctly and smoothly i would buy them in an instant!
Well, the problem may not be the sql at all. It may be your design.
You should maybe add a timer with 500 ms. This timer runs the query.
Now when you type something in your textfield, you set timer mode to 1.
this resets timer and causes the timer to fire 500 ms after the last keystroke.
On each search, buffer 20-50 records and search that for the next match before going back to the database.
As the info in the field gets longer, you’ve got a higher probability that the next match is already in memory. It really smooths things out.