sql query in thread clarification

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

Cheers
Andrew

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.

Cheers

@Tobias Bussmann Thanks.
Im new to sql. How do you not do a prepared sql statement? Cheers

i don’t think i am:

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 )

you should use prepared statements as soon as possible.

ok, maybe i should try a different database other than postgresql?

maybe you want to show us your test project first?

Sure. Will send a link soon. Will hardcore the database settings. It’s just a test dev one from heroku.

Here is a test project that show my code and the result.
Im not displaying the results of the query here, just issuing the query.

https://dl.dropboxusercontent.com/u/45282540/test.xojo_binary_project

Cheers

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.

Ok. Apparently it needs to be zipped?
https://dl.dropboxusercontent.com/u/45282540/test.zip

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!

Cheers

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.

Another possible approach:

  1. Don’t search on the first two characters.
  2. 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.

hmm, i like that Tim. that is very simple!
I am also going to have a with to turn on/off live search (as i call it)