I have a database-centric app with an SQLite database on the device. Recently I’ve noticed that the speed of querying one table in particular has quite dramatically slowed down. This has coincided with the addition of about 10 columns to that table, taking the total columns to 43. Is there a point at which SQLite becomes inefficient due to the number of columns in a table? Or is this likely just a coincidence?
small cache?
When I create the database in code I execute:
db.SQLExecute("PRAGMA page_size = 20000")I’d actually look at vacuum and analyze as both can have an impact on query planning
Vacuum - http://sqlite.org/lang_vacuum.html
Analyze - http://sqlite.org/lang_analyze.html
Additionally did you add indexes etc when you added the 10 columns ?
If not and you use them for queries then you might consider adding indexes then analyzing as the query planner will do a better job
Yes I added Analyze after doing some searching here and I run it after creating my database. I do have indexes on my tables but not on the additional columns that seemed to slow down the performance; those columns aren’t searched. My database is filled from calls into our web services and it’s used as a cache whereby most records are never deleted so I don’t think Vacuum will help that much. But I will work out a way to add it in - the trick is getting the app to do it at appropriate times.
I wonder if the table has crossed some sort of line in terms of columns and is using more memory now? Some of the logs from our internal testers are showing low memory warnings.
Really hard to say much with any authority without knowing the schema
Lots of blob columns can have a negative effect on any db - not just sqlite on iOS
An iOS device does have lots of storage but not that much memory for running the OS , your app and everything else
Loading up a db into memory may actually hurt more than help
Increasing cache size of sqlite may mean your program has to swap more, not less (odd but since this RAM is taken up by the db cache its not available to the rest of your process)
Thanks Norman. There was a blob column in that table which I have removed as I wasn’t even using it. That doc on memory management is interesting and a bit frightening at the same time. I currently do nothing when I receive a low memory warning other than logging it. I’ll have to work out what, if anything, I can unload…
One way to simplify would be to create separate blob tables that relate to the table that needs a blob. They’d be accessible when you need them…