I am writing to ask if the new release of Xojo (2014r2) will help to increase speed on existing sqlite database files. Sometimes the application gets slow, and the GUI is blocking for a while, and I guess this happens more importantly when the user has a larger database file on his computer.
In this regard, is it relevant to rebuild all apps with the new release?
Make sure you run analyze periodically
Database queries are optimized based on statistics the engine gathers about the data
If those stats are old or incorrect you can get a less than optimal query plan
One of the first bug reports we had in the new Betas related exactly this situation & quite literally running analyze foxed the problem - very dramatically
Made no other changes other that running analyze & the speed returned and in fact was slightly better
If anyone, by any means, introduced a DB design forcing the engine to solve queries in a sequential search, a DB engine upgrade will not introduce any significant speed enhancement. The solution for those cases could be a next generation of your software, using an enhanced new better designed structure, with proper indexes, and you creating a DB converter from the old version to the new optimized version.