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'sonDestroy
method).and you use the following to get your helper :-
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) anddb.endTransaction();
to complete the transaction. Note that transactions don't nest, so when nesting transactions you have to add code so that thebeginTransaction
,setTransactionSuccessfull
andendTransaction
are only applied once.The follow is an example that caters for nesting :-
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
andendTransaction
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.