Suggestions to improve SQLite perfromance

Question

I am saving data to an SQLite Database. It's taking a while for small amounts of data to be saved. I'm using: beginTransaction(); setTransactionSuccessful();, endTransaction(); etc but it doesn't improve performance. I'm considering switching to RealmDB if I can't improve this. Does anyone have any tips? Cheers

public enum DbSingleton {
    INSTANCE;
    private DatabaseHandler db;
    public Context context;


    private DatabaseHandler getDatabaseHandler(Context context) {
        if (db != null) {
            return db;
        } else {
            if (MainActivity.mainActivity == null) {

                SQLiteDatabase.loadLibs(context);

                return db = new DatabaseHandler(context); //make static context field in area this is used. e.g. main
            } else {
                return db = new DatabaseHandler(MainActivity.mainActivity);
            }
        }
    }


    //will provide one sample for reference now

    public void insert(Context context, String table, ContentValues values) {
           SQLiteDatabase.loadLibs(MainActivity.mainActivity);
        //note this line
        SQLiteDatabase sql = getDatabaseHandler(context).getWritableDatabase(DatabaseHandler.DB_PASSWD);
        try {
            sql.beginTransaction();


            sql.insert(table, null, values);
            // Log.i("Values being sent to db", values.toString());

            sql.setTransactionSuccessful();
            sql.endTransaction();
        } catch (SQLiteException ex) {
            Log.e("SQL EXCEPTION", ex.toString());
        } finally {
            sql.close();
        }
    }
    public Cursor select(Context context, String statement, String[] selectArgs) {
        SQLiteDatabase sql = getDatabaseHandler(context).getReadableDatabase(DatabaseHandler.DB_PASSWD);
        if (selectArgs == null) {

            return sql.rawQuery(statement, null);

        } else {
            return sql.rawQuery(statement, selectArgs);
        }

    }

    public int Update(Context context, String table, ContentValues values, String where, String[] whereArgs) {

        SQLiteDatabase sql = getDatabaseHandler(context).getWritableDatabase(DatabaseHandler.DB_PASSWD);
        int count = -1;

        try {
            sql.beginTransaction();
            count = sql.update(table, values, where, whereArgs);
            sql.setTransactionSuccessful();
            sql.endTransaction();
        } catch (SQLiteException ex) {
            Log.e("SQL EXCEPTION", ex.toString());
        }

        if (count == 0) count = -1;
        return count;

    }
    public void Drop(Context context, String table) {
        SQLiteDatabase sql = getDatabaseHandler(context).getWritableDatabase(DatabaseHandler.DB_PASSWD);

        sql.execSQL("DROP TABLE IF EXISTS " + table);

    }
    public void Create(Context context, String table) {

        SQLiteDatabase sql = getDatabaseHandler(context).getWritableDatabase(DatabaseHandler.DB_PASSWD);
        sql.beginTransaction();
        sql.execSQL(table);
        sql.setTransactionSuccessful();
        sql.endTransaction();
    }

Answer 1

Android provides a new library as part of the architecture components called Room.

official doc says:

The Room persistence library provides an abstraction layer over SQLite to allow for more robust database access while harnessing the full power of SQLite.

Room Persistence Library

Save data in a local database using Room

More:

You can use the room with another awesome library (Paging Library) to handle paging and huge data sets

Paging library

Answer 2

Wrapping your inserts in beginTransaction() and endTransaction() is only saving time when you do multiple inserts.

So always save your data to one table at once using the following format, this greatly improves performance:

ArrayList<String> itemsToInsert; //an array of strings you want to insert
db.beginTransaction();
ContentValues values = new ContentValues(1);
for (int i = 0; i < itemsToInsert.size(); i++) {
    values.put('field', itemsToInsert.get(i));
    db.insert(table, null, values);
}
db.setTransactionSuccessful();
db.endTransaction();

In addition, for selecting from a table, query() is performing slightly better than rawQuery(), but the difference is small.

---

Als check this article for more background information about SqlLite Performance: sqlite-insertions