iPhone SQLite connection in domain object - close each time?

Question

I have what I would consider a small sized iPhone app that uses SQLite.

There is a singleton domain object which gets data from a SQLite database.

Is it better to create and open the SQLite connection for each request, or to open the DB once and hold on to it for the duration of the app.

The app's reason for being is the domain object so other objects will not need the DB.

Answer 1

I would generally go with opening the database just once and holding a reference to it in your singleton object. As to whether this makes a big performance difference will depend on how often you make requests. If you are requesting data for each cell in a tableView for example I would expect that opening the database only once would make a big difference.

By opening only once you could also keep references in your singleton to prepared SQL statements so that you do not need to compile them each time. Each time you make a request first check if you have the prepared statement already compiled. If not you make a call to sqlite3_prepare_v2 to compile it and save the resulting statement in your singleton.

if (request_stmt == nil) {
    const char *sql = "SELECT ...... WHERE xxx LIKE ?";
    sqlite3_prepare_v2(sqldb, sql, -1, &request_stmt, NULL);
}

You can release these statements by calling sqlite3_finalize when you release your singleton.