Hi there.
Working on a online database, I have to select a big number of rows from a recordset. I put that method in a thread and it works nice, but the user interface is “blocked” when my thread executes the sqlselect query (returns about 2000 rows with 50 fields) for about one minute.
But it’s inside a thread… Is it possible?
In any case, do anyone has suggestions to avoid or reduce this lag?
Thanks a lot
the xojo database plugins does not work in threads. you have to use the MBS sql plugins to have it work in threads.
or rethink your database work to download small chunks at a time.
use the update query to make the work on the server, and so NOT download that big data size ?
[quote=431177:@Jean-Yves Pochez]the xojo database plugins does not work in threads. you have to use the MBS sql plugins to have it work in threads.
or rethink your database work to download small chunks at a time.
use the update query to make the work on the server, and so NOT download that big data size ?[/quote]
They work in threads just fine. They just dont always yield to other threads. PostgreSQLDatabase has a MultiThreaded property, for example.
@Sergio Tamborini What database type?
[quote=431182:@Thom McGrath]They work in threads just fine. They just dont always yield to other threads. PostgreSQLDatabase has a MultiThreaded property, for example.
@Sergio Tamborini What database type?[/quote]
Now is mysql, but I work a lot with cubesql server
Thanks to all for your answers. I think I have two praticable ways: use a LIMIT command in the query and repeat it until the records are ended, or use MBS plugins… if they woks with cubesql server too it may be a good solution.
Thanks to all.
Ok. A word of advice too. Use a connection dedicated to each thread. The database server doesn’t care about your app’s threading, so sharing a connection is a recipe for unusual behaviors such as two threads executing statements intermixed with each other. Locking could do the job too, but is more error-prone, less robust, and more work to setup.
Not “just fine” but they work. As long as the Connect is not Thread Safe, it’s not good enough. 
<https://xojo.com/issue/54692> (Created 01/18/2019)
There is a difference between thread safe and thread yielding. Most things in Xojo are thread safe because it uses cooperative threads. Something that isn’t thread safe causes problems, such as accessing the UI from a thread. That isn’t thread safe. Methods that do not yield are still safe, just annoying.
@Sergio Tamborini The feedback case posted by @Sascha S made me look at the MySQLCommunityServer docs more closely and both SQLSelect and SQLExecute are listed as thread yielding. Are you sure it’s these calls blocking your main thread?
no matter what database you are using and where in the app you are doing it, you should always use a limit and get the pages (or you called them chunks) in a loop. grab a chunk, parse it, grab the next one parse it, repeat until done.
as developers we test with small data sets and everything is wonderful. then we get the clients/customers involved and now they have a bazillion entries and the code/app breaks down. if we plan for them having such large datasets up front, then it doesnt matter if they have 1 row or 1 billion rows. our code/app can handle it.
I can not speak for MySQL per se as I dont deal with that database but cubeSQL, SQLite and PostgreSQL all work in threads.
thanks!
–sb
MBS Xojo SQL Plugin does Connect and SQLSelect/SQLExecute threaded fi needed.
Also you can set timeouts for most databases, e.g. on MySQL to detect lost connection quicker.