Ok, update on the SQLite situation. Likely not a Xojo bug but a change in SQLite behavior.
I use a view to union 4 tables together, as they represent multiple subclasses. So the view represents the parent class and allows me to query any of the subclassed objects without knowing precisely what they are.
By switching the view from UNION to UNION ALL, nearly all of the performance came back. Got it to 30 seconds, compared to 25 in 2023r4. I think there’s still things that could be tweaked, but it’s already far better than 793 seconds.
So I think it’s safe to say the SQLite situation is user error. Still have the threads being slow on Windows issue, but at least one problem is solved.
So I decided to circle back to this because it’s a good idea that I hadn’t considered before you mentioned it. Did a test in a simple project and saw a speed improvement of about 60%, which motivated me to put the effort into it. I updated one of the processes to use entirely prepared statements, fighting with Xojo’s stupid prepared statement API the whole time. I ended up making my own wrapper class just so I can keep some sanity. And it works.
Except I saw no performance improvement at all. +/- half a second compared to baseline. And before you ask, yes, I’m reusing the same statement object for every similar statement.
Amazing. I expected the 60%+ outcome. However, it kinda depends upon what’s the slow part of the system. Give you already said SQL, I expected a bigger overall effect.
We had an app that split up giant Excel spreadsheets into SQLite databases. Prepared statements gave us an 8* speed gain. There and back.
Isn’t there a newer perpared statement api that works the same at as standard statements. Ie no binding of types and use of param arrays for the data. I’m not at desktop at the moment. Our swap to prepared was fairly trivial.
I wrote a test app to use a cooperative thread to insert random records into a SQLite database with write-ahead logging and then read the random records back into an array of objects. There are two constants to choose whether INSERTs are done in the usual way or via a prepared statement and whether columns from the SELECT are referenced by name or index. Some quick results for 1M rows, compiled, aggressive optimization, presented in thousands of rows per second:
INSERT
Baseline: 44.1k/s
Prepared Statement: 77.1k/s
SELECT
Column Names: 742.0k/s
Column Index: 800.9k/s
Not sure how this translates to real-world gains, but it demonstrates that prepared statements and numeric indexing is faster as I would expect. Here’s the project if you want to experiment with it.
Regarding the issue on Windows of what appears to be threads being allocated time instead of priorities and how this plays out when the active thread is just sitting there idle:
Does it make sense to use a higher thread priority on Windows and then manually yield every X milliseconds via YieldToNext? My expectation is that it would utilize the idle cycles while also keeping the UI responsive. There might be some combination of priority and yield interval that results in similar behavior to the older Xojo version.
Kind of. There is SQLitePreparedStatement.Bind which takes a variable number of arguments, but it’s barely useful. First of all, it does nothing at all to help with the types. Second of all, unlike ExecuteSQL and SelectSQL, if you pass an array of variants, it tries to pass that array instead of expanding the array.
Yeah I saw the same thing in synthetic benchmarks. But not in the real world. Which basically just means my SQL usage is not a bottleneck.
The threads I’m dealing with are already loop heavy, so there’s not really anything to be gained by yielding even more. YieldToNext is only really useful when you have a lack of automatic yields.
Yielding on loop boundaries has always seemed a little iffy, so I would lean towards Travis’ suggestion myself. And that behavior seems to vary from release to release, at least on Windows.
Have you tried doing explains on your queries? Maybe the queries are unoptimized such that you cause full scans etc? Also do you have any triggers in your DB? Those are often quite slow as well especially if there are multiple cascaded inserts/deletes
I am trying something first. The app was written a long time ago but it appears that 2025 has problems when there is a mixture of old and new API controls.
Some crashes have been stopped by updating the control to API2 as error messages after compile reported (API1) properties and methods did not exist even though they didn’t produce error messages in the IDE.
I haven’t got around to converting the whole project to API2.
I’m presently updating the whole (very large) project but it’s going to take some time.
Definitely not zero benefit, but it depends on context. One of my processes doesn’t see much of any improvement. But another goes from tolerable to lightning fast.
The fact that CriticalSection can now be used in both cooperative and preemptive threads makes the conversion much more practical.
Does anyone know of a well-written open source project using API 1.0 that might be a good representation of the difficulties in migration to API 2.0? I’ve considered writing a guide or making a video about API 2.0 conversion but can’t use my closed source projects. It’s been years since I’ve done an API 2.0 migration, but I don’t remember it being that difficult. I think that’s probably because I lean heavily on regular expressions to apply changes in bulk, and I can imagine that being very useful information for others.
I’m not proposing using it to yield more but to maintain the same frequency of yielding with a higher thread priority. Essentially, if the main thread is receiving too much time, force it to receive less time by giving the task thread a higher priority. Then use manual yielding to maintain the same level of responsiveness.
I tried adapting your threaded hashing example to monitor for context changes and the amount of time spent in each thread. I focused on compiled apps, aggressive optimization, Windows x64, real hardware.
Xojo 2023.4, Thread Priority 5
Mean time in main: 22.5 ms
Mean time in thread: 81.9 ms
Context change rate: 19.1/s
Hash rate: 23.8k/s
Xojo 2025.2.1, Thread Priority 5
Mean time in main: 54.8 ms
Mean time in thread: 80.5 ms
Context change rate: 14.8/s
Hash rate: 17.7k/s
So here we see that both versions are spending about 80 milliseconds in the thread performing the hashing before switching back to the main thread. The compile from the newer Xojo is spending more time in the main thread (being idle) before switching back to the hashing thread. Another way of looking at it is the older compile is spending about 78% of the time hashing and the newer compile is spending about 59% of the time hashing. This difference accounts for the difference in hash rates.
Xojo 2025.2.1, Thread Priority 1000, Sleeping 1ms Every 80ms, Wake Early
To try my idea I set the thread priority to 1000. Typically this would choke the responsiveness, so we have to manually yield back to the main thread. I tried yielding after 80 ms to match the behavior of the previous tests.
I initially proposed YieldToNext, but I didn’t consider that won’t necessarily cause a context switch if the priorities suggest staying in the current thread. Instead I tried sleeping for 1ms with the WakeEarly option set to true.
Mean time in main: 15.2 ms
Mean time in thread: 79.9 ms
Context changes per second: 21.0/s
Hash rate: 24.6k/s
So compared to the original 2023.4 compile, I was able to keep the time in the hash thread the same, reduce the amount of time spent in the main thread, increase the number of context changes per second, and increase the hash rate.
Note that my observed results do not show the main thread and task thread receiving equal time for equal priorities. I don’t think this matches your thesis that it is naively allocating time as is a potential interpretation of the documentation.
Let’s try it…
Xojo 2025.2.1, Thread Priority 45
Mean time in main: 50.7 ms
Mean time in thread: 735 ms
Context change rate: 2.55/s
Hash rate: 28.8k/s
So it results in a roughly 94/6 split, but it does that by extending the task thread to 735 ms while keeping the main thread time the same. This kills the responsiveness.
In this example the time measurement takes about 0.17% of the time used to compute one hash. The logic could potentially be optimized by only checking the elapsed time when a counter rolls over.