Is It possibile call an async sqlexecute ?
I use mysql and i need to make bulk insert (insert of many records at the same time)
I have created a strong insertStr likes this "insert into table (field1, field2, field3) values (Array of ( field1,2,3)) "
But when i call mysqldb.sqlexecute(insertStr)
Called in a thread, the app stop for 3 or 4 seconds …
The destination tablet has 800.000.000 records and his size Is 190Gb with a lot of indexes
Is possibile made an async insert ?
I recall you need the mbs database plugins to have an async insert.
xojo doesn’t support it.
may be @Christian_Schmitz can confirm.
also
instead of making 800M calls to sqlexecute, you can make 1000 lines of text with inserts
it will take almost as mush time as one line call
it will reduce the global time by 1000x
You could also use a helper or series of helper apps.
If this is a one time job,
you could also construct a file containing all INSERT statements and then use a Database tool such as MySQLWorkbench to insert all rows.
I use a lot of threads and the db Is istanzied in every thread but the app stop
No i can’t
The app Is server sockets and recives much data in each connection
Send your data to a helper app, fast, maybe writing to a SQLite intermediary DB, in blocks to avoid hiccups, 1 to 100 records each block, and your helper will work in background reading your local source and inserting them into the MySQL.
Why the use of worker Is Better then the use of threads ?
Actually i m using a rotation of 30 imstanced threads
Because “threads” in xojo are a scam, they arent “real” (preemptive) threads that can run in parallel, they are “Cooperative” threads that run in the same thread as the GUI, sharing time slices of a single phisical core (Yes, just one). A worker is a separate app than can run in parallel using a different core.
The fake cooperative threads are not implemented in the best way. When you Run the thread, the app inmediatly switches to execute that thread, leaving the Main one (App GUI) frozen until the app decides to asign some time slice to the GUI again.
Maybe this is what is happening in your app, try using a CallLater to run the thread. This way, the main thread will finish the current sub avoiding the unresponsive app.
Wow, just look at your CPU usage, most probably, a single very bussy core while the rest of them are idle.
Because Xojo does not have threads but fibers named as threads. There’s only one real thread in a Xojo app, and when some part of the app is busy, other part is suspended. No parallelism, just time slicing. A helper allows parallelism, it takes a separate real thread and maybe even in another CPU core.
You may better go and do 1000 at a time to keep things responsible.
You may run them on a Xojo thread, a worker, a console app to be able to show a progress bar while doing it.
MBS Xojo SQL Plugin can run statements on background thread (preemptive thread).
Workers are a separate console app which can run on an entirely separate core whereas Xojo threads are part of the same app, run on the same core and take time away from the rest of the app.
thanks everyone for the directions
another (probably stupid) question
in the worker you can define the number of cores to use, what happens if I indicate (8) more than the CPU (that has 4 core ) on which the application is running
That’s the maximum number of cores to use
Ok
but if i set 8 tò worker and the cpu has 4 cores
What appen ?
Worker use only 4 cores or create e queque ?
You’ll only get 4 workers at any given time.
You may also want to consider setting the maximum percentage so you don’t saturate the machine.
Is there the possibility (with xojo code ) to intercept how many cpu and relative cores there Ares on the machine where run the app ?
To set the cores of worker at runtime
On Windows you could explore the register path:
HKEY_LOCAL_MACHINE\HARDWARE\DESCRIPTION\System\CentralProcessor
Under it you should find one entry for each virtual CPU (total hardware threads)
E.g. The CPU has 4 cores, each core handles 2 threads, you will find 8 Logical Processors (vCPU)
Exactly the same as with any other app. More than one app is executed in the phisical core sharing time.
It is better to make that an user selectable option, that way the user can define the number of helpers. Maybe someone dont want to use all the cores.