sqlite3_enable_shared_cache and sqlite_backup_init slowing execution on iPhone

Question

I have some relatively complex sqlite queries running in my iPhone app, and some are taking way too much time (upwards of 15 seconds). There are only 430 or so records in the database. One thing I've noticed is that opening a new database connection (which I only do once) and stepping through query results (with sqlite3_step()) causes sqlite3_backup_init() and sqlite3_enable_shared_cache() to run, which take 4450ms and 3720ms respectively of processing time throughout the test period. I tried using sqlite3_enable_shared_cache(0); to disable shared caching before opening the database connection, but this seems to have no effect.

Does anyone have any idea how to disable these so I can get some sort of speed improvement?

Answer 1

Well, I suppose this doesn't directly answer the question, but part of the problem was my use of a cross join instead of a left join. That reduced the query time from about 4000ms to 60ms. Also, the backup_init function is no longer called and enable_shared_cache doesn't spin as much.

Answer 2

Almost the same issue. My app crashes at sqlite3_backup_step() which is called from sqlite3_step(). Have you solved this issue?

Answer 3

I fixed my app by replacing inner join with left join. My data allows it. If your data does not allow it consider adding a where clause

apples a inner join bananas b on b.id=a.id

vs.

apples a left join bananas b on b.id=a.id where b.id is not null