I like to provide a search filter in my app so that when the user types a search word, I look up the results in the background and show them as soon as I have them. Now, these lookups can take a second, as it does full text search with the “LIKE” operator in a large sqlite3 database (so, I can’t simply index the data to make the search faster), where it often finds 100s of records out of many 1000s.
If I perform a search after every keystroke, and wait for the result, the typing gets sluggish, so even if the user wants to type a long search word, he has trouble typing it in quickly.
Hence I like to see if I can perform the search in the background so that the main process doesn’t get blocked.
One way would be to do the query using a Shell in asynchronous mode, issuing the sqlite3 command that way.
I wonder if there are other ways to do this? I could use declares to access the sqlite3 lib directly, but that would still be blocking since it would be running in Xojo’s (rather non-)cooperative thread model. This could only work if the sqlite would perform regular callbacks during its search from which I could then let Xojo’s threading do some yielding. But I could not find such callbacks in the sqlite libs.
Any other ideas how to solve this?
In fact, I think a plugin would be useful for this. The plugin would run the query in a pre-emptive thread. Hmm…
I also use a Timer set to something like 750 ms to initiate the search. Every keystroke resets the timer so the UI remains responsive until the user pauses in their typing.
Kem, your suggestion is a work-around, not the solution to deal with blocking queries.
But Christian is on to something. If Xojo does use a secondary pre-emptive thread for the query, while waiting (and yielding) in the thread it was called from, then doing my queries in a thread would indeed unblock the UI as I want. I’m testing this now…
Argh, this damn forum. I meant to edit my post, but I made a quote - and when I noticed my mistake too late, I could not go back and edit my original any more.
I wonder if killing the thread would be safe here because it would only kill the co-op thread that waits for the pre-emptive sqlquery thread to finish, i.e. the query would continue in its own thread. That might leave some leaks…
How else would you do it, start a new thread each time? You could end up with quite a few queries running simultaneously that way.
Oh, I just had a thought. Have a single thread that is constantly running in a loop. It will mostly sleep when there is no query, else run the query based on a flag. When done, it will store its results in a property and set a flag unless there is another query waiting. In that case, it would discard its just-found results and run the next query. As the user types, all you’re doing is setting that flag. If the query takes a second, the user might type 2-10 more characters before the next query has to run (although I still recommend the Timer to avoid extra processing). You won’t have to kill the current query, in case that’s a danger, and will minimize the number of useless queries.
By the way, if you can, consider Valentina as your database instead. You might avoid the whole issue that way.
[quote=54662:@Thomas Tempelmann]I like to provide a search filter in my app so that when the user types a search word, I look up the results in the background and show them as soon as I have them. Now, these lookups can take a second, as it does full text search with the “LIKE” operator in a large sqlite3 database (so, I can’t simply index the data to make the search faster), where it often finds 100s of records out of many 1000s.
[/quote]
Don’t use like - set up and FTS index
Its way faster & does a much better job
[quote=54662:@Thomas Tempelmann]If I perform a search after every keystroke, and wait for the result, the typing gets sluggish, so even if the user wants to type a long search word, he has trouble typing it in quickly.
[/quote]
Dont do that
The Find pane in the IDE waits .25 seconds THEN runs the search so as you type it does not immediately delay things
And it does it in a thread so when the user types another keystroke it can kill the thread & wait again
[quote=54662:@Thomas Tempelmann]Hence I like to see if I can perform the search in the background so that the main process doesn’t get blocked.
[/quote]
Use a thread - all the db plugin yield if you run them in a thread
That means you leave the thread to run the entire query - regardless of how long it takes since the thread does not get any callbacks until the query is done.
Norman, could you clarify if the sqlite plugin does use a helper thread that runs the query pre-emptively?
If not, I cannot see how it would not block the main thread because the sqlite query (i.e. the sqlite3 lib’s execute function) can’t do any yielding.
But if it’s a pre-emptive helper thread for the query, then how would killing the RB thread would also take care of killing the helper thread? AFAIK, a killed thread cannot do any cleanup, or can it?
[quote=54691:@Norman Palardy]Don’t use like - set up and FTS index
Its way faster & does a much better job[/quote]
Oh, I no no idea of this FTS index feature. Thanks.
Most, if not all, of the plugins now use some mechanism to yield time back to the UI so it can be run in a thread & keep the UI responsive. The details naturally vary from db to db.
REAL SQL Database did this for some time and SQLite Database does as well.
See http://documentation.xojo.com/index.php/Changes_2013r3 CHANGE 28366 Database: MySQLCommunityServer, ODBCDatabase, and MSSQLServer: removed the Multithreaded property. A SQLSelect or SQLExecute statement is now implicitly executed pre-emptively dependent on whether or not it was called from a Xojo thread.
[quote=54718:@Norman Palardy]Most, if not all, of the plugins now use some mechanism to yield time back to the UI so it can be run in a thread & keep the UI responsive. The details naturally vary from db to db.
REAL SQL Database did this for some time and SQLite Database does as well.[/quote]
I’m not sure the Xojo SQLITE engine yield time to other threads in some cases, in particular for the sqlite timeout.
I did a test in a web application with SQLITE (WAL mode). So no UI but all other threads / sessions are blocked (thus also sessions that display web pages).
A session launches a tread. This thread opens a new SQLITE connection, and then starts a long write for several minutes (long enough for testing). During this writing loop, the thread is sleeping regularly. I also did tests by changing the value of db.threadYieldInterval (the “sqlite3_progress_handler” indicated by Norman) and thread.priority.
Meanwhile, another session starts a db reading in a new thread/new db connection. Ok, no errors, everything works.
All sessions are reactive. During the write operation, I can also navigate through the webPages, open multiple sessions, etc. Everything works.
But if a session request to write to the database (in a new thread/new db connection), All sessions (and thus all clients/browsers pages) will be hanging for the timeout. Of course, this write request is to be refused, that’s normal, SQLITE handling only one writing simultaneously. But why the entire application, all sessions, all threads are blocked for the timeout (10 seconds by default)?
Note that this problem does not arise if each session is initiated with a new instance of the web app. There, everyting works. The SQLITE timeout does not block webApps/sessions/threads. As all instances communicating with the same SQLITE database, I wonder if there is not a problem in the xojo-sqlite bridge, or in the Xojo SQLITE compilation settings.
Currently, it seems that multiple processes (ie multiple xojo apps) can communicate with SQLITE, without SQLITE timeout blocks other processes. But if multiple threads of the same process (a single xojo app) tries to communicate with SQLITE, this poses a problem in some cases, including when the sqlite engine is busy during the timeout. If this is it, this is serious.
couple of years ago I used DoEvent only… until this was doomed. Today I put such tight loops or queries in a new thread calling App.YieldToNextThread from the inside. To keep an progressbar updated or a Windows responsible I do the communication between thread and UI with a Timer and helper vars like CurrentItem or MaxItems.
This is the Timer.Action Event. “SyncThread” is the Name of the Thread and “Progress” the name of the Progress bar.
if SyncThread.State = Thread.NotRunning then
if MaxItems < 0 then
MaxItems = 0
else
Progress.Value = Progress.Maximum
end if
else
Progress.Maximum = MaxItems
Progress.Value = CurrentItem
end if
Tomas thank you, but for the application frozen during the sqlite timeout, it does not work.
[quote=54712:@Norman Palardy]@Thomas Tempelmann If not, I cannot see how it would not block the main thread because the sqlite query (i.e. the sqlite3 lib’s execute function) can’t do any yielding.
