46

I have an android application that access local sqlite3 db frequently, for performance consideration so i always keep the connection open. But one of my friends recommended me to open/close the connection every operation.

1) What's your guys opinion on these two methods ? cons/pros. 2) I did some testing and find the DB connection doesn't have too much perf overhead. Does the performance overhead of DB connection varies on the size of the DB ?

jim.huang
  • 1,052
  • 2
  • 9
  • 14
  • There is an compromiss, I would say, every time activity with DB operations, not application itselfs, dies?! – Tima Jan 30 '11 at 10:48
  • Maybe a better answer: don't close it unless you have to. http://stackoverflow.com/a/7739454/423105 – LarsH Feb 07 '17 at 21:08

4 Answers4

33

I don't know of any performance penalties in frequent closing/opening of the database (regardless of its size). I think the answer to this question also depends on what type of application is accessing the database.

Do you "re-query" the database a lot?
Then it seems rectified to keep it open.

Do you fetch different data each time you fetch something?
Again, it seems reasonable to leave it open (as you won't gain in caching the data instead).

Are there any other applications accessing the same database?
If there is a risk for concurrency or blocking issues, it might be wise to close the database after finished reading/writing from/to it.

Generally I would say that you might gain more in caching data than in leaving the database open (contra closing it) when optimizing for performance.

dbm
  • 10,376
  • 6
  • 44
  • 56
  • Thanks dbm. My application is the only one who access the DB, and it did a lot of re-query. Seems for me always keep the DB open is the better choice. But could you be more specific about the performance gain by "Caching data", i'm not quite sure about what the sqlite database did under the ground. – jim.huang Jan 30 '11 at 14:59
  • 1
    I assume (maybe wrongfully) that you query the database, read the values you need from the returned Cursor object and then throw it away. Next time you need the data you go through the same procedure again. Now, assume that the nature of your retrieved data wouldn't require you to refresh it between the consecutive database queries. Then it would be an alternative to read from the database once and store the values in a local HashMap or something. Accessing an "in-memory" data structure (the "cached" data) is generally much faster than re-querying the database. – dbm Jan 30 '11 at 16:10
  • Now there are situations where you really do need to re-query the data (hence, caching isn't necessarily an option). Then it might be interesting to have a look at "indexing", even "prepared statements" can be of interest where supported by the database engine. This link might also be interesting: http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html – dbm Jan 30 '11 at 16:17
6

If you are using an in memory database then your data will be discarded when you close the connection.

A bit of an edge case perhaps, but it just caught me out.

David Sykes
  • 48,469
  • 17
  • 71
  • 80
  • 2
    This is actually a really good answer to know of. I just recently had a successful experience with using an in-memory database as a cache infrastructure (where I explicitly wanted to discard the cache on exit)... – dbm Dec 17 '13 at 07:13
  • Once you open the db/connection, it's already cached anyways. – stdout Sep 24 '15 at 08:11
3

The documentation says the connection can be open as long as you need it. And can be closed in onDestroy() method. Documentation link

Persisting Database Connection:

Since getWritableDatabase() and getReadableDatabase() are expensive to call when the database is closed, you should leave your database connection open for as long as you possibly need to access it. Typically, it is optimal to close the database in the onDestroy() of the calling Activity.

    @Override
    protected void onDestroy() {
        mDbHelper.close();
        super.onDestroy();
    }
Seagull
  • 1,063
  • 1
  • 11
  • 18
2

As an addition, opening & closing a connection so frequently might possibly cause you to experience notorious SQLite exceptions, if you access db from multiple threads.

See, if you access db from multiple threads even over a single connection and since those operation are not atomic, then you may try to update db which was closed just before by another thread.

stdout
  • 2,471
  • 2
  • 31
  • 40