3

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.

Improve this question

6 Answers 6

Reset to default
8

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks Stephan. But if I do this and there are some queries which have not been executed completely, will the operation rollback or will it be completed and then the database closed?
If you use transactions with BEGIN ... COMMIT, the transaction will ROLLBACK if the database is closed.
Hi Stephen. Closing a db and opening again is giving this exception stackoverflow.com/questions/449933/…
5

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.

Improve this answer

1 Comment

Thanks Stephen. I am not caching any data from the database and all the data is read only when it is needed. Resets are also done as soon as a statement's function is over. I am also finalizing statement. Any other suggestions? Thanks again.
4

There are also PRAGMAs to modify the cache size.

Improve this answer

2 Comments

PRAGMAs aren't going to help on the iPhone, as Apple provides the binary lib.
PRAGMAs are executed at run time, not compile time
3

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.

Improve this answer

1 Comment

This is because of the way SQLite uses its page cache. If you put INSERT/UPDATE statements in a transaction it makes the modifications in memory only and then writes them in bulk when you COMMIT. If you don't have an explicit transaction SQLite puts an implicit transaction around each INSERT, UPDATE, or DELETE. Also, SQLite always updates an entire 4k page of the DB even if you've only modified a single bit of data in that page.
2

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.

Improve this answer

1 Comment

Should I do this while execute every sql transation??
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.