is it even possible, just tried some lines of code and it seems to thread nothing.
doing an sqlselect on 100k rows, distant server so this takes time and I don’t get user response. the thread is on the sqlselect and the app is unresponsive !
thanks.
What type of database are you trying to query from?
postgres
Yeah, that happened to me too, a large query, put it on thread and block the GUI (on windows), my solution: use LIMIT x OFFSET y on query. any another idea…?
I already used limit and offset. I’m on a Mac.
but this time I need to get all the rowids in an array, once I open the table.
so I wanted to make a thread to get in the background them as it is too long. but it does not seems to work ?
from the documentation http://documentation.xojo.com/index.php/PostgreSQLDatabase
[quote] Threading
SQLSelect and SQLExecute statements do not block when called from within Threads. [/quote]
so this should work …
The thread is on the same window that is not responsive. is it the problem ?
do I have to instantiate the thread at the App level, or in another window ?
Are you yielding within your thread?
sorry James, I don’t understand your question … can you reformulate please ?
See the documentation for thread:
[quote]Threads can yield time to other threads and other applications each time they execute a looping construct such as For, While, and Do. However, a thread does not necessarily yield time at every loop boundary even though it has an opportunity to do so. A thread actually yields to another thread when the Thread Scheduler decides that its timeslice has expired. Context switches are expensive, so the Thread Scheduler always tries to avoid them.
You can also force context switches by calling Application.YieldToNextThread or by calling Application.SleepCurrentThread.[/quote]
However, this wouldn’t make a difference here because your select is atomic. So try with the offset and the yielding between the calls.
In my thread, I only do the sqlselect, that returns 100k rowids and this takes time.
( around 20 seconds the database is across the internet)
then I have a loop that copies each rowid inside an array.
the thread is stuck at the sqlselect, and the app is unresponsive until it.
then the loop is fast enough it’s not noticeable for the user.
just saw this : http://documentation.xojo.com/index.php/PostgreSQLDatabase.MultiThreaded
so my sqlselect calls should be threaded by themselves …
why do I have to wait my 100k rowids for 20 seconds then ?
I’d bet that the thing you’re waiting on is the data transmission at the end and there’s not much that can be done there. This is part of the reason that http apis are suggested for querying remote servers (besides the obvious security reasons that is).
Probably time to rethink your design… why do you need to load all the ids into memory in the first place???
to handle the user selection in a listbox (that is already pagined with offset and limit)
I’m ok to wait for the data to arrive, but why does a thread ( with a long sqlselect inside) hang my app user interface ? it should not.
Make sure the thread has a low priority.
Where there are a couple of possibilities:
- If they are just integers then simple let the user type in the id rather than select it
- If it is a string then let the user type it and to a like query to bring down the nearest 10 or so
- Try to introduce come kind of delta concept - first time down load the lot and after that only transfer deltas
- Spin of a console application to do the download so it will run on any other processor you have.
Right, but what I’m saying is that the query portion doesn’t block, but the underlying socket receiving the data may be.
Now it could simply be that what you are dealing with is a bug, and therefore worth a report. I am not familiar enough with the plugin code to tell you either way, but a report would get someone who is to make a judgement.
I use PostgreSQL with my WebApps. All users locked up on all web sessions when I accessed the database in a thread until I changed to SQLDatabaseMBS and use db.SQLSelectMT(“SELECT ”). Not sure if that’s an option for you though.
Right, it should not, but does. This is a known Bug, see <https://xojo.com/issue/37806> and <https://xojo.com/issue/43107>