Memory management when using sqlite database in iphone

Question

Hi

My application makes use of a SQLite database to store the user's inputs. The number of records in the input would go to around 100 records and I have a lot of SQL operations going on in the application.

When I run the application with Object Allocations, it shows the sqlite library libsqlite3.0.dylib using up a lot of memory. Does the libsqlite framework lead to memory leakage? What is the best way to communicate with database? Having a lot of sql calls is increasing the memory usage of my app.

Can someone please let me know what the best way to use sqlite in an app effectively. (I am using the SQLiteBooks example as the reference)

Thanks.

Answer 1

Sqlite uses a cache for requests. Close & reopen the database from time to release cache memory.

You shouldnt care unless your memory requirements are high.

You can catch critical conditions in the UIApplicationDelegate method applicationDidReceiveMemoryWarning or UIViewController delegate method didReceiveMemoryWarning

If one of these methods is called, close & reopen the database.

Answer 2

I've not seen any memory leaks caused by sqlite. It does use a reasonable chunk of memory, but think of how much code you'd need to write and data you'd need to cache to do the same thing...

The best advice is to use efficient SQL and reset your statement handles as soon as possible. Finalizing your prepared statements might also help, though I've not found the need to do that.

People often recommend periodically closing and reopening the database. While this won't hurt I've not seen any practical benefit myself.

Finally, on the sqlite website you'll see talk of functions to manage memory. These sound quite seductive until you realise they're optional and are not enabled in the default build on the iPhone.

Answer 3

There are also PRAGMAs to modify the cache size.

Answer 4

I've seen memory usage spike before when I had a relatively large database because of poor indexing. If you add a few well thought out indexes to your database, it is a quick and easy way to get memory usage back in the real world.

Answer 5

I've had memory usage shoot up in SQLite when doing many INSERTs (> 1000) in a row. Write performance was also slow. These issues were almost completely eliminated by wrapping the loop doing the INSERTs in a transaction. I posted some sample code for this in response to this question.

Answer 6

Sorry I'm not "well reputed" enough to make a comment or up vote but I just wanted to say that the pragma thing worked for me :

I have a database which is basically composed of 200 elements, and the database file is about 2.1mb.

With the default cache-size (2000 pages - see sqlite website), after doing my queries (getting all the elements actually), I discovered with the Object Allocation Intrument tool that I had a little more than 2mb memory allocated by (I guessed) sqlite. This could mean that all the elements have been cached by sqlite, couldn't it ?

With a cache size of 100 pages ("PRAGMA cache-size = 100"), this quantity was reduced to 900kb !!

So, thank you for the tip !

Answer 7

You really want to limit the cache size of sqlite in iphone applications. When you launch your application and initialize your database run a command like:

const char *pragmaSql = "PRAGMA cache_size = 50";
if (sqlite3_exec(database, pragmaSql, NULL, NULL, NULL) != SQLITE_OK) {
    NSAssert1(0, @"Error: failed to execute pragma statement with message '%s'.", sqlite3_errmsg(database));
}

this will prevent sqlite from caching your queries and slowly consuming all of your memory.