how to make SQLite connections more efficient

Question

Is there any advantage in keeping a local sqlite connection open the entire time that the activity is running?

I usually create a new connection and then close it within every method that does a database operation. It would look something like this :

myMethod(Context context){
    LocalDBHelper localDBHelper = new LocalDBHelper(context); //extended SQLiteOpenHelper 
    SQLiteDatabase db = localDBHelper.getWritableDatabase();
    ...
    localDBHelper.close();
}

So in a typical user session, this would happen around 10 times. Would it make sense to create a connection in onResume(), use that in all the database access methods and finally close it in onPause()?

Answer 1

Is there any advantage in keeping a local sqlite connection open the entire time that the activity is running?

You usually want one "connection" for the entire life of your process. In particular, you do not want to have multiple "connections" in use simultaneously across multiple threads. All of the thread-safety logic for SQLite in Android is based around using a single SQLiteDatabase (and, hence, single SQLiteOpenHelper) for all of those threads, so proper locking can be done.

Answer 2

Based on @CommonsWare answer, i have implemented a Singleton pattern to have a single application wide instance of LocalDBHelper using lazy instantiation. It works fine till now and avoids the need to instantiate and close the helper/database for every operation.

public class MyApplication extends Application{
    private static MyApplication instance;
    public MyApplication(){
        instance = this;
    }
    public static Context getContext(){
        return instance;
    }
}

public class LocalDBHelper extends SQLiteOpenHelper{
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "MyDB";
    private static final String LOG_TAG = "LocalDBHelper";

    private static LocalDBHelper instance = null;
    /*private constructor to avoid direct instantiation by other classes*/
    private LocalDBHelper(){
        super(MyApplication.getContext(), DATABASE_NAME, null, DATABASE_VERSION);
    }
    /*synchronized method to ensure only 1 instance of LocalDBHelper exists*/
    public static synchronized LocalDBHelper getInstance(){
        if(instance == null){
            instance = new LocalDBHelper();
        }
        return instance;
    }
    ...
    ...
}

Usage :

SQLiteDatabase db = LocalDBHelper.getInstance().getWritableDatabase();
db.insert(...)

Usage with transactions :

SQLiteDatabase db = LocalDBHelper.getInstance().getWritableDatabase();
db.beginTransaction();
try{
....
...
db.setTransactionSuccessful();
}catch(Exception e){
    e.printStackTrace();
}
finally{
    db.endTransaction();

}

Important : No need to call localDBHelper.getInstance().close() anywhere

Answer 3

As your app works on the same machine every time it is luanched and the same memory is accessed every time, so there will be no problem to let it be open. because in the same memory space , sqlite just loads like a DLL for a main application.

Just one problem may occur! when you wanna run many threads for accessing database at the same time(for example with AsyncTask) the interference between them forces some threads to stop! so its better to make connection for new threads eachtime!

Answer 4

Personally I find it easier to call the SQL Connections required on the initial app load, to store into the SQLite DB's, and either set a refresh button for the user to decide when they want to refresh the data, OR set a periodic update interval timer for the application.

By doing it this way, you are increasing performance during general usage of the app by placing the data load on a pre/user defined time.

Although I do suppose this depends on how often a DB Interaction is going to be performed....

This Question may ahve some useful answers for you:

Should I open() and close() my SQL database constantly or leave it open?