When to open and close a SQlite DB in the iOS lifecycle?

Question

I run a series of queries throughout the lifecycle of my app.

I am currently using FMDB (An Objective-C wrapper around the Sqlite C API), and I am opening and closing before every query.

FMDatabase * db = [FMDatabase databaseWithPath:pathToMyDB];
[db open]
FMResultSet * s = [db executeQuery:@"SELECT * FROM myTable"];
// Use FMResultSet
[db close];

Open and close trigger fopen() and fclose() lower down, so I belive I can gain a perforce win by keeping the database open.

However, I believe temporary objects will build up, which could lead to memory issues. Closing the database clears the temporary objects.

• When should I open and close the database connection? (e.g. Application did enter backgound?)
• Should I run VACUUM in low memory situations?

Answer 1

Database doesn't create and/or keeps any objects (or at least it shouldn't). The C API is just a handy way to use SQL queries but once those are executed they should be released.

Now, for the returning objects of a query, those are just copied into your FMResultSet. Once you release that they're gone.

Imo, if the database itself is not too big, you should keep a reference to it in your app delegate (guarantee to be alive in the lifetime of your app). When you enter the app/resume from background open the database and when you go into background/close just close it.

Keep in mind that AppDelegate is a singleton ( i think) and you can access your database using (AppDelegate*)[[UIApplication sharedApplication]delegate].your_db from anywhere in your app to perform the actual queries.

Using VACUUM will crash your app if you try to call it when you get memory warning. Think about this: to rearrange the database it should be loaded into memory (or at least a part of it), if you already have memory warning...poof...crash.

You could test if your database keeps objects in memory. Just perform like 100 queries at 1s interval (or a button press) and watch in instruments where and if the memory grows. You may have a leak in your app ( or in the wrapper itself) and you blame it on the database.