SQLite Optimization in Android Programming – SQLite Optimization in Android Apps

SQLite

SQLite is a popular, light weight and open source database. SQLite combines a clean SQL interface and it is renowned for its low memory usage. Though data integrity is the first priority of its creator, it has a decent speed. Now a days SQLite is more popular with its usage in Android, Apple’s iOS etc.

In this article we are covering SQLite optimizations in Android application programming. These SQLite optimizations will enhance the performance and reduce the resource (like memory, processor cycles etc) consumption.

Use BEGIN TRANSACTION & END TRANSACTION for SQLite Optimization

Each SQL statement is enclosed in a new transaction block by SQLite runtime, by default. Sowhen you perform a basic DB operation such as INSERT, a transaction block will be created and wrapped around it.

Letting SQLite runtime manage the transaction for you is advisable only if your routine performs only one DB operation on a data set. However, if you are doing  numerous DB operations (say INSERT inside for loop), this becomes very expensive, since it requires reopening, writing to, and closing the journal file for each statement. A Journal file is a temporary file created that holds intermediate tables (In Android, you can see this file being created and destroyed whenever you perform a DB operation).

You can avoid this and take an explicit control of transactions by wrapping sequences of SQL statements with BEGIN TRANSACTION; and END TRANSACTION; statements. This speedup is also obtained for statements which do not alter the database.

Please note: If you take control of managing a transaction as explained here, then you are responsible to flag a commit or rollback the transaction as desired.

In Android application development, which uses Java syntax, the following Example illustrates how to use BEGIN TRANSACTION; and END TRANSACTION;

db.beginTransaction();
try{
 for(int i =0; i< LENGTH ; i++,sequenceNum++)
 {
 // execute SQL
 }
 db.setTransactionSuccessful();// marks a commit
}
finally{
 db.endTransaction();
}<em> </em>

Advantages of Using BEGIN TRANSACTION & END TRANSACTION in SQLite

  • The speedup and improvements we will get by this simple tweak are significant.

Disadvantages of Using BEGIN TRANSACTION & END TRANSACTION in SQLite

  • Since you are managing the transaction, you need to take care of commit or rollback which otherwise, SQLite would have taken care of.

Use INDEXES in SQLite

If Indexing was never performed in the database, when you search for a record in an unsorted dataset using a projection query, a sequential search would have to be done. However, this is generally not the case: every database, including SQLite performs indexing on the dataset which reduces the search time.

Indexes maintain a sorting order on a column or set of columns in a table. This allows selecting a range of values without having to scan the entire table on disk. Indexes can make a huge difference in speed when a query does not need to scan the entire table.Indexes are implemented by creating a separate index table which maps a key created from the column[s] to a row index in the indexed table. So there is an additional size overhead, which is usually worth the cost. Especially, if you are going to perform a lot of read/search operation on your database.

SQLite automatically creates an index for every UNIQUE column, including PRIMARY KEY columns, in a CREATE TABLE statement. You can also create explicit indices by executing the CREATE INDEX query.

Please Note: If you have more complex queries that can’t make use of any indexes that create, you can de-normalize your schema, structuring your data in such a way that the queries are simpler and can be answered using indexes.

Advantages of using INDEXES in SQLite

  • If use INDEX judiciously, the search time for a record will be lowered significantly and queries that require a search operation will run faster.

Disadvantages of using INDEXES in SQLite

  • INDEXES in SQLite requires creation of extra temporary tables which will consume more storage.
  • INSERT operations become slow since it requires creation of index tables and their sorting. However, to avoid this, if you are doing a large number of INSERTs, try dropping indexes and recreating them at the end. The gain in execution time however needs to be noted. Opt for this only if the improvements are really significant.

Use positional arguments in Where clause in SQLite

It is advisable that when building a compiled query in SQLite make use of ‘?’ to bind your arguments. There are certain advantages of using ‘?’ instead of directly inserting values using string concatenation.

Advantages using positional arguments in Where clause in SQLite

  • It becomes easy to pre-compile the queries and cache them. Not only the queries but SQLite is optimized to use same query plan, in such cases.
  • No cache limit for compiled SQL is hit. Allegedly, SQLite, by default, tries to cache all the queries that it executes. If you keep building dynamic queries, there is a possibility of cache overflow.
  • Possibility of SQL injection is eliminated: SQLite runtime guards against this abuse.

You Might Also Like

Leave a Reply

Your email address will not be published. Required fields are marked *


two × = 14

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

CommentLuv badge