I have just changed my database from SQLite to SQLCipher. And my app became unbelievably slow. It takes ages to take click actions. I studied and found out these two possible reasons:
Do not repeatedly open and close connections, as key derivation is very expensive, by design. Frequent opening / closing of the database connection (e.g. for every query) is a very common cause of performance issues that can usually be easily resolved using a singleton database connection.
Use transactions to wrap insert / update / delete operations. Unless executed in a transaction scope, every operation will occur within it's own transaction which slows things down by several orders of magnitude.
About the first point, Can some one please explain what does it mean by opening and closing connections repeatedly. I think me using SQLiteDatabase db = this.getWritableDatabase("secure_key); for every Query is the problem. Any example on How can I use a singleton database connection class for this will be a great help.
About the second point, how can I use wrapper for the mentioned queries and will it be useful?
In regard to point 1) the following code, within the DBHelper, creates a singleton connection (note I only close the db in the Main Activity's onDestroy method).
/**
* Instantiates a new Db helper.
*
* @param context the context
*/
DBHelper(Context context) {
super(context, DBConstants.DATABASE_NAME, null, 1);
}
private static DBHelper instance;
/**
* Gets helper.
*
* @param context the context
* @return the helper
*/
static synchronized DBHelper getHelper(Context context) {
if(instance == null) {
instance = new DBHelper(context);
}
return instance;
}
and you use the following to get your helper :-
dbhelper = DBHelper.getHelper(context);
db = dbhelper.getWritableDatabase();
With regard to 2 you use, db.beginTransaction(); to begin a transaction, db.setTransactionSuccessful(); to mark it as successful after making the DB changes (this is required in order for the transaction to be applied. Otherwise, ending the transaction will effectively negate any changes applied) and db.endTransaction(); to complete the transaction.
Note that transactions don't nest, so when nesting transactions you have to add code so that the beginTransaction, setTransactionSuccessfull and endTransaction are only applied once.
The follow is an example that caters for nesting :-
void deleteAisle(long aisleid, boolean intransaction) {
if (doesAisleExist(aisleid)) {
if (!intransaction) {
db.beginTransaction();
}
String whereargs[] = {Long.toString(aisleid)};
// Delete ProductUsage rows that have Aisle as a parent
pudeletes = db.delete(
DBProductusageTableConstants.PRODUCTUSAGE_TABLE,
DBProductusageTableConstants.PRODUCTUSAGE_AISLEREF_COL +
" = ?",
whereargs
);
// More done here but removed for brevity (including deletion of the Aisle)
if (!intransaction) {
db.setTransactionSuccessful();
db.endTransaction();
msg = "DB Transacion SET and ENDED for Aisle ID=" + Long.toString(aisleid);
}
}
}
The above may be called individually but if deleting a shop then it could be called multiple times, in which case it would be called with **intransaction ** being true (so the beginTransaction, setTransactionSuccessful and endTransaction would be skipped and left to be done by the parent).
As for usefulness, you'd only use transactions if doing a number of actions together.