CODEWITHSUNDEEP
iphonesqlite
lostInTransit
lostInTransit

Reputation: 70997

Memory management when using sqlite database in iphone

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.

Upvotes: 3

Views: 10050

Answers (6)

Carl Coryell-Martin
Carl Coryell-Martin

Reputation: 3510

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.

Upvotes: 2

Brad Larson
Brad Larson

Reputation: 170319

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.

Upvotes: 3

Justin Williams
Justin Williams

Reputation: 723

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.

Upvotes: 1

Doug Currie
Doug Currie

Reputation: 41180

There are also PRAGMAs to modify the cache size.

Upvotes: 4

Stephen Darlington
Stephen Darlington

Reputation: 52565

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.

Upvotes: 5

Stephan Burlot
Stephan Burlot

Reputation: 5077

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.

Upvotes: 8

Related Questions