SQLite: Don't Forget Vacuum!

Databases
1

I have a database based app that uses foreign keys extensively and does a lot of cross table selects from views, but It has only about 15 tables. That largest 3 table have ~116,000, ~26,000 and ~10,000 records . Records are regularly added but VERY seldom deleted or modified in normal use.

To add new features, I made a bunch of changes to a lot of the DB records, added a few more tables using with foreign keys and made some changes to the Views and SQLSelects made from Xojo as well as some of the xojo code itself … I did not think any of the changes i made to the DB structure or the selects would degrade performance.

But when I tried running the app it took 11X longer to produce a key graph (felt like it took forever and was totally unacceptable)!

By profiling I saw what was taking longer was the DB.SQLSelect statement… So I assumed that something I changed in the DB structure or the SQLSelect statements was the cause… I have been banging my had against the wall for 2 days to figure out what change was causing the slowdown. I have been trying to undo the changes I made in those one by one to find the cause…

It was only AFTER I exhausted that avenue that I remembered all the changes to the records themselves!!!

I ran a Vacuum and i got all of the old performance back with the changes in place… actually it was slightly faster!

Moral of the story: Don’t forget vacuum!

2

but also don’t forget that Vacuum will (could) alter RowID values unless you named your own AutoIncrement Integer Primary Key

3

I always do!

4

In addition to that, you might get an optimization by running ANALYZE on your SQLite (or of course CubeSQL) database.

[quote]The ANALYZE command gathers statistics about tables and indices and stores the collected information in internal tables of the database where the query optimizer can access the information and use it to help make better query planning choices.

Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.[/quote]