android - 提高 SQLite 性能的建议

Question

我正在将数据保存到 SQLite 数据库。保存少量数据需要一段时间。我正在使用:beginTransaction();setTransactionSuccessful();, endTransaction();等等，但它不会提高性能。如果我不能改进这一点，我正在考虑切换到 RealmDB。有没有人有任何提示？干杯

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

在 beginTransaction() 和 endTransaction() 中包装您的插入只会在您进行多次插入时节省时间。

因此请始终使用以下格式一次将数据保存到一个表中，这会大大提高性能:

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();

此外，对于从表中进行选择，query() 的性能略优于 rawQuery()，但差异很小。

---

还可以查看这篇文章以了解有关 SqlLite 性能的更多背景信息: sqlite-insertions