Should I open and close DB for every DB operation in sqlite?

Question

Hi I am developing android app. It has 9 tables currently I have created one instance to open db and using the same throughout the whole project. I don't know whether this is causing my UI to freeze. should I open and close the DB every time when I do DB operation or what I am doing is correct?

\**   to fetch records**\
public synchronized SQLiteDatabase readDatabase() {
    if(mOpenCounter == 1) {//to open the database only one time
        // Reading
      mDatabase = mDatabaseHelper.getReadableDatabase();

    }
    return mDatabase;
}


\**   to open db **\
public synchronized SQLiteDatabase openDatabase()throws SQLException {

    mOpenCounter+=1;
    if(mOpenCounter == 1) {//to open the database only one tim

        mDatabase = mDatabaseHelper.getWritableDatabase();

    }

    return mDatabase;
}

UPDATE Hi From 2 days I am working on this.When I go through several links on google I found we need to do db operations on separate thread Can some one give me example of how to do this.Its not like one query I have 9 tables each table has around 8 queries.Is each query should be run in seperate thread?

My code structure is like I have created a separate class for each table query for example for Table A,TableAqueries all queries regarding this table comes here.Please give me a solution to run the db operations in separate thread. Please help me

Please help me

Answer 1

should i open and close the db every time when i do db operation

No.

I dont know whether this is causing my ui to freez

If you are doing database I/O on the main application thread, that will cause your UI to freeze while that I/O is being completed. Please do disk, database, and network I/O on background threads.

Answer 2

Opening and closing the database frequently is more likely to introduce performance issues as closing the databases requires an "expensive" call to getWriteableDatabase or getReadableDatabase to re-open the database.

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.

Additionally cached data will be lost, so there could be an impact due to the cache having to be rebuilt.

Persisting Database Connection

As such closing the database is unlikely to fix a freeze and to the contrary may have the potential to contribute to the freeze.

I would suggest checking all of your loop constructs, if nothing stands out then I'd suggest gradually commenting out/disabling code until the App runs without freezing the cause will then be highlighted.