SQLite Version 3.8.5

Hello,

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?

Thanks for your opinion,
Payam

Most times slownesses are just due bad database designs. Are you worrying about indexes optimizations on key fields avoiding sequential searches?

It’s difficult to change database structures at this time on all existing databases. Can the new sqlite engine enhance the speed of database operations?

Also SQLite is not maintenance free. You still need to VACUUM and ANALYZE periodically. Usually the last steps after a backup so you have a good backup to work against.

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

I must study this issue. What effects can have an analyse command? Or does the analyse command give information on how to improve the database?

I wrote complicated apps, and yet I do not know about such basic things. I always liked Xojo for this reason, it makes everything work fine, but I don’t know exactly what I’m doing, when I code.

The links I gave give the details of what analyze does.

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.

About Indexes: http://www.sqlite.org/queryplanner.html
About optimizations: http://www.sqlite.org/optoverview.html

I’m in the same boat.

I write scientific software. Xojo is a programming language, but that doesn’t help me with the Science - that is a separate matter and I have to learn about it.

I’ve also started on databases. Xojo makes it easy to USE databases, but it is NOT a database. Therefore I have to learn about databases as well.

You might want to have a look at “I wish I knew how to … program SQLite with Xojo” - see http://scispec.ca/index.php/books/25-sqlite-with-xojo-desktop

Thanks everyone for all your help :-))