SQLite
Most applications won't be heavy users of SQLite, and therefore you very likely won't have to worry too much about performance when dealing with databases. However, you need to know about a few concepts in case you ever need to optimize your SQLite-related code in your Android application:
- SQLite statements
- Transactions
- Queries
NOTE: This section is not intended to be a complete guide to SQLite but instead provides you with a few pointers to make sure you use databases efficiently. For a complete guide, refer to www.sqlite.org and the Android online documentation.
The optimizations covered in this section do not make the code harder to read and maintain, so you should make a habit of applying them.
SQLite Statements
At the origin, SQL statements are simple strings, for example:
- CREATE TABLE cheese (name TEXT, origin TEXT)
- INSERT INTO cheese VALUES (‘Roquefort', ‘Roquefort-sur-Soulzon')
The first statement would create a table named “cheese” with two columns named “name” and “origin”. The second statement would insert a new row in the table. Because they are simply strings, the statements have to be interpreted, or compiled, before they can be executed. The compilation of the statements is performed internally when you call for example SQLiteDatabase.execSQL, as shown in Listing 1–18.
Listing 1–18. Executing Simple SQLite Statements
SQLiteDatabase db = SQLiteDatabase.create(null); // memory-backed database
db.execSQL(“CREATE TABLE cheese (name TEXT, origin TEXT)”);
db.execSQL(“INSERT INTO cheese VALUES (‘Roquefort', ‘Roquefort-sur-Soulzon')”);
db.close(); // remember to close database when you're done with it
NOTE: Many SQLite-related methods can throw exceptions.
As it turns out, executing SQLite statements can take quite some time. In addition to the compilation, the statements themselves may need to be created. Because String is also immutable, this could lead to the same performance issue we had with the high number of BigInteger objects being allocated in computeRecursivelyFasterUsingBigInteger. We are now going to focus on the performance of the insert statement. After all, a table should be created only once, but many rows could be added, modified, or deleted.
If we want to build a comprehensive database of cheeses (who wouldn't?), we would end up with many insert statements, as shown in Listing 1–19. For every insert statement, a String would be created and execSQL would be called, the parsing of the SQL statement being done internally for every cheese added to the database.
Listing 1–19. Building a Comprehensive Cheeses Database
public class Cheeses {
private static final String[] sCheeseNames = {
“Abbaye de Belloc”,
“Abbaye du Mont des Cats”,
…
“Vieux Boulogne”
};
private static final String[] sCheeseOrigins = {
“Notre-Dame de Belloc”,
“Mont des Cats”,
…
“Boulogne-sur-Mer”
};
private final SQLiteDatabase db;
public Cheeses () {
db = SQLiteDatabase.create(null); // memory-backed database
db.execSQL(“CREATE TABLE cheese (name TEXT, origin TEXT)”);
}
public void populateWithStringPlus () {
int i = 0;
for (String name : sCheeseNames) {
String origin = sCheeseOrigins[i++];
String sql = “INSERT INTO cheese VALUES(”” + name + ””,”” + origin +
””)”;
db.execSQL(sql);
}
}
}
Adding 650 cheeses to the memory-backed database took 393 milliseconds on a Galaxy Tab 10.1, or 0.6 microsecond per row.
An obvious improvement is to make the creation of the sql string, the statement to execute, faster. Using the + operator to concatenate strings is not the most efficient method in this case, and it is possible to improve performance by either using a StringBuilder object or calling String.format. The two new methods are shown in Listing 1–20. As they simply optimize the building of the string to pass to execSQL, these two optimizations are not SQL-related per se.
Listing 1–20. Faster Ways to Create the SQL Statement Strings
public void populateWithStringFormat () {
int i = 0;
for (String name : sCheeseNames) {
String origin = sCheeseOrigins[i++];
String sql = String.format(“INSERT INTO cheese VALUES(”%s”,”%s”)”, name,
origin);
db.execSQL(sql);
}
}
public void populateWithStringBuilder () {
StringBuilder builder = new StringBuilder();
builder.append(“INSERT INTO cheese VALUES(””);
int resetLength = builder.length();
int i = 0;
for (String name : sCheeseNames) {
String origin = sCheeseOrigins[i++];
builder.setLength(resetLength); // reset position
builder.append(name).append(“”,””).append(origin).append(“”)”); // chain
calls
db.execSQL(builder.toString());
}
}
The String.format version took 436 milliseconds to add the same number of cheeses, while the StringBuilder version returned in only 371 milliseconds. The String.format version is therefore slower than the original one, while the StringBuilder version is only marginally faster.
Even though these three methods differ in the way they create Strings, they all have in common the fact that they call execSQL, which still has to do the actual compilation (parsing) of the statement. Because all the statements are very similar (they only differ by the name and origin of the cheese), we can use compileStatement to compile the statement only once, outside the loop. This implementation is shown in Listing 1–21.
Listing 1–21. Compilation of SQLite Statement
public void populateWithCompileStatement () {
SQLiteStatement stmt = db.compileStatement(“INSERT INTO cheese VALUES(?,?)”);
int i = 0;
for (String name : sCheeseNames) {
String origin = sCheeseOrigins[i++];
stmt.clearBindings();
stmt.bindString(1, name); // replace first question mark with name
stmt.bindString(2, origin); // replace second question mark with origin
stmt.executeInsert();
}
}
Because the compilation of the statement is done only once instead of 650 times and because the binding of the values is a more lightweight operation than the compilation, the performance of this method is significantly faster as it builds the database in only 268 milliseconds. It also has the advantage of making the code easier to read.
Android also provides additional APIs to insert values in a database using a ContentValues object, which basically contains the binding information between column names and values. The implementation, shown in Listing 1–22, is actually very close to populateWithCompileStatement, and the “INSERT INTO cheese VALUES” string does not even appear as this part of the insert statement is implied by the call to db.insert(). However, the performance of this implementation is below what we achieved with populateWithCompileStatement since it takes 352 milliseconds to complete.
Listing 1–22. Populating the Database Using ContentValues
public void populateWithContentValues () {
ContentValues values = new ContentValues();
int i = 0;
for (String name : sCheeseNames) {
String origin = sCheeseOrigins[i++];
values.clear();
values.put(“name”, name);
values.put(“origin”, origin);
db.insert(“cheese”, null, values);
}
}
The fastest implementation is also the most flexible one as it allows more options in the statement. For example, you could use “INSERT OR FAIL” or “INSERT OR IGNORE” instead of simply “INSERT”.
NOTE: Many changes were made in Android 3.0's android.database and android.database.sqlite packages. For instance, the managedQuery, startManagingCursor, and stopManagingCursor methods in the Activity class are all deprecated in favor of CursorLoader.
Android also defines a few classes that can improve performance. For example, you can use DatabaseUtils.InsertHelper to insert multiple rows in a database while compiling the SQL insert statement only once. It is currently implemented the same way we implemented populateWithCompileStatement although it does not offer the same flexibility as far as options are concerned (for example, FAIL or ROLLBACK).
Not necessarily related to performance, you may also use the static methods in the DatabaseUtils class to simplify your implementation.
Transactions
The examples above did not explicitly create any transaction, however one was automatically created for every insertion and committed immediately after each insertion. Creating a transaction explicitly allows for two basic things:
- Atomic commit
- Better performance
The first feature is important but not from a performance point of view. Atomic commit means either all or none of the modifications to the database occur. A transaction cannot be only partially committed. In our example, we can consider the insertion of all 650 cheeses as one transaction. Either we succeed at building the complete list of cheeses or we don't, but we are not interested in a partial list. The implementation is shown in Listing 1–23.
Listing 1–23. Insertion of All Cheeses in a Single Transaction
public void populateWithCompileStatementOneTransaction () {
try {
db.beginTransaction();
SQLiteStatement stmt = db.compileStatement(“INSERT INTO cheese
VALUES(?,?)”);
int i = 0;
for (String name : sCheeseNames) {
String origin = sCheeseOrigins[i++];
stmt.clearBindings();
stmt.bindString(1, name); // replace first question mark with name
stmt.bindString(2, origin); // replace second question mark with origin
stmt.executeInsert();
}
db.setTransactionSuccessful(); // remove that call and none of the changes
will be committed!
} catch (Exception e) {
// handle exception here
} finally {
db.endTransaction(); // this must be in the finally block
}
}
This new implementation took 166 milliseconds to complete. While this is quite an improvement (about 100 milliseconds faster), one could argue both implementations were probably acceptable for most applications as it is quite unusual to insert so many rows so quickly. Indeed, most applications would typically access rows only once in a while, possibly as a response to some user action. The most important point is that the database was memory-backed and not saved to persistent storage (SD card or internal Flash memory). When working with databases, a lot of time is spent on accessing persistent storage (read/write), which is much slower than accessing volatile memory. By creating the database in internal persistent storage, we can verify the effect of having a single transaction. The creation of the database in persistent storage is shown in Listing 1–24.
Listing 1–24. Creation of Database On Storage
public Cheeses (String path) {
// path could have been created with getDatabasePath(“fromage.db”)
// you could also make sure the path exists with a call to mkdirs
// File file = new File(path);
// File parent = new File(file.getParent());
// parent.mkdirs();
db = SQLiteDatabase.openOrCreateDatabase(path, null);
db.execSQL(“CREATE TABLE cheese (name TEXT, origin TEXT)”);
}
When the database is on storage and not in memory, the call to populateWithCompileStatement takes almost 34 seconds to complete (52 milliseconds per row), while the call to populateWithCompileStatementOneTransaction takes less than 200 milliseconds. Needless to say, the one-transaction approach is a much better solution to our problem. These figures obviously depend on the type of storage being used. Storing the database on an external SD card would make it even slower and therefore would make the one-transaction approach even more appealing.
NOTE: Make sure the parent directory exists when you create a database on storage. See Context.getDatabasePath and File.mkdirs for more information. For convenience, use SQLiteOpenHelper instead of creating databases manually.
Queries
The way to make queries faster is to also limit the access to the database, especially on storage. A database query simply returns a Cursor object, which can then be used to iterate through the results. Listing 1–25 shows two methods to iterate through all the rows. The first method creates a cursor that gets both columns in the database whereas the second method's cursor retrieves only the first column.
Listing 1–25. Iterating Through All the Rows
public void iterateBothColumns () {
Cursor c = db.query(“cheese”, null, null, null, null, null, null);
if (c.moveToFirst()) {
do {
} while (c.moveToNext());
}
c.close(); // remember to close cursor when you are done (or else expect an
exception at some point)
}
public void iterateFirstColumn () {
Cursor c = db.query(“cheese”, new String[]{“name”}, null, null, null, null,
null); // only difference
if (c.moveToFirst()) {
do {
} while (c.moveToNext());
}
c.close();
}
As expected, because it does not have to read data from the second column at all, the second method is faster: 23 milliseconds vs. 61 milliseconds (when using multiple transactions). Iterating through all the rows is even faster when all the rows are added as one transaction: 11 milliseconds for iterateBothColumns vs. 7 milliseconds for iterateFirstColumn. As you can see, you should only read the data you care about. Choosing the right parameters in calls to query can make a substantial difference in performance. You can reduce the database access even more if you only need up to a certain number of rows, and specify the limit parameter in the call to query.
TIP: Consider using the FTS (full-text search) extension to SQLite for more advanced search features (using indexing). Refer to www.sqlite.org/fts3.html for more information.