1. Basic SQLite Statements
The basic SQLite statements are simple strings like-
- CREATE TABLE phone(devicename TEXT, company TEXT)
- INSERT INTO phone VALUES('M8','HTC')
The above statements are just simple strings & they have to be interpreted,or compiled before they can be executed. The compilation of these statements is down internally when we call the
The following code demonstrates how these statements are executed.
The problems with the above implementation is that SQLite statements takes some quite some time to execute. In addition to the compilation, the statements themselves may need to be created. String is also immutable(String is immutable means that you cannot change the object itself, but you can change the reference ) ,which poses performance issues here.
2. The INSERT Statement String
INSERT is the most used statement in any SQLite database as you are bound to insert several entries into the database.A basic implementation of inserting several values into the database is shown below.
The problem with the above implementation is that for every insert statement, a String would be created and execSQL would be called.This prcedure takes a lot of time and memory.
One solution for this problem is to avoid using the + operator for String concatenation & instead use a StringBuilder object or calling String.format.The two new methods are shown below
3. The Compile Statement
Now since we have addressed the issue of using & interpreting the sting statements, the next issue is of execSQL method.The execSQL does the actual compilation(parsing) of the statement.In the above examples the execSQL statement is called for every insertion to the database. All this can be avoided by only compiling the statement once & binding later.This is depicted below.
4. Using ContentValues
As we avoided the use of execSQL staement above, the same can be achieved using a ContentValues object. Its not better than the Compile statement method. but either way its an elegant method of getting things done.
Transactions are automatically created when we perform any read/write from the the database. But creating explicit transaction can improve the performance tremendously.The implementation of explicit transaction on compile statement is depicted below.